Har en sql sträng: Du kan inte sortera frågorna var för sig, utan sorteringen gäller för det sammanslagna resultatet. Däremot tror jag att du ska kunna gruppera dem var för sig.Group By och Order By i Union
<code>
SELECT 'Arbetstid' AS Kategori,'Närvaro' AS UnderKat,dbo.Company.CompanyName AS Företagsnamn,dbo.Employee.Department AS Avdelning, dbo.Employee.EmployeeName AS Namn, dbo.Presence.SalaryArtPres AS Lönetyp,
ROUND(SUM(CAST(REPLACE(dbo.Presence.CalcPresTime,',', '.') AS float)),2) AS Beraknadtid,ROUND(CAST(REPLACE(dbo.Employee.WorkTimeWeek, ',', '.') AS float)/7,2) as Normtid
FROM dbo.Presence INNER JOIN
dbo.Employee ON dbo.Presence.EmployeeNr = dbo.Employee.EmployeeNr INNER JOIN
dbo.Company ON dbo.Employee.CompanyNr = dbo.Company.CompanyNr
WHERE (dbo.Employee.Department = 'MARKNAD') And dbo.Presence.PresenceDate BETWEEN '2005-01-01' And '2005-01-30' And dbo.Employee.PersonalKategory = '01'
UNION
SELECT 'Frånvaro' AS Kategori,'Frånvaro' AS UnderKat,dbo.Company.CompanyName AS Företagsnamn,dbo.Employee.Department AS Avdelning, dbo.Employee.EmployeeName AS Namn, dbo.Absence.SalaryArtAbs AS LöneTyp,
ROUND(SUM(CAST(REPLACE(dbo.Absence.CalcAbsTime,',', '.') AS float)),2) AS Beraknadtid, '0' As Normtid
FROM dbo.Absence INNER JOIN
dbo.Employee ON dbo.Absence.EmployeeNr = dbo.Employee.EmployeeNr INNER JOIN
dbo.Company ON dbo.Employee.CompanyNr = dbo.Company.CompanyNr
WHERE (dbo.Employee.Department = 'MARKNAD') And dbo.Absence.AbsenceDate BETWEEN '2005-01-01' And '2005-01-30' And dbo.Employee.PersonalKategory = '01'
</code>
Varför kan jag inte göra Group BY och Order By på båda selectarna? På detta sätt:
<code>
SELECT 'Arbetstid' AS Kategori,'Närvaro' AS UnderKat,dbo.Company.CompanyName AS Företagsnamn,dbo.Employee.Department AS Avdelning, dbo.Employee.EmployeeName AS Namn, dbo.Presence.SalaryArtPres AS Lönetyp,
ROUND(SUM(CAST(REPLACE(dbo.Presence.CalcPresTime,',', '.') AS float)),2) AS Beraknadtid,ROUND(CAST(REPLACE(dbo.Employee.WorkTimeWeek, ',', '.') AS float)/7,2) as Normtid
FROM dbo.Presence INNER JOIN
dbo.Employee ON dbo.Presence.EmployeeNr = dbo.Employee.EmployeeNr INNER JOIN
dbo.Company ON dbo.Employee.CompanyNr = dbo.Company.CompanyNr
WHERE (dbo.Employee.Department = 'MARKNAD') And dbo.Presence.PresenceDate BETWEEN '2005-01-01' And '2005-01-30' And dbo.Employee.PersonalKategory = '01'
GROUP BY dbo.Company.CompanyName,dbo.Employee.Department,dbo.Employee.EmployeeName,dbo.Presence.SalaryArtAbs
Order BY dbo.Employee.EmployeeName
<code>
Och sen..
<code>
UNION
SELECT 'Frånvaro' AS Kategori,'Frånvaro' AS UnderKat,dbo.Company.CompanyName AS Företagsnamn,dbo.Employee.Department AS Avdelning, dbo.Employee.EmployeeName AS Namn, dbo.Absence.SalaryArtAbs AS LöneTyp,
ROUND(SUM(CAST(REPLACE(dbo.Absence.CalcAbsTime,',', '.') AS float)),2) AS Beraknadtid, '0' As Normtid
FROM dbo.Absence INNER JOIN
dbo.Employee ON dbo.Absence.EmployeeNr = dbo.Employee.EmployeeNr INNER JOIN
dbo.Company ON dbo.Employee.CompanyNr = dbo.Company.CompanyNr
WHERE (dbo.Employee.Department = 'MARKNAD') And dbo.Absence.AbsenceDate BETWEEN '2005-01-01' And '2005-01-30' And dbo.Employee.PersonalKategory = '01'
GROUP BY dbo.Company.CompanyName,dbo.Employee.Department,dbo.Employee.EmployeeName,dbo.Absence.SalaryArtAbs
Order BY dbo.Employee.EmployeeName
</code>
Kan man bara gruppera efter UNION:en? I så fall kan man ju inte gruppera om man har mer än en select. Eller?????Sv: Group By och Order By i Union
Du kan i och för sig göra en sortering i en nästlad select om du absolut behöver det. Typ:
select * from (select ... order by ...)
union
select * from (select ... order by ...)
order by ...
Tips: Ifall du inte är ute efter att få bort dubbletter mellan första och andra frågan så är det effektivare att använda "union all" än "union", just därför att den inte gör denna matchning mellan resultaten.