我只是发出一个组,在其中指定 xml 数据,然后出现错误GROUP BY 子句中不允许使用 XML 方法。
这是我的sql
SELECT HourSheetID,(MAX(RowID)+1) as "RowID",
XMLData.value('(Log/EntryDate)[1]','datetime') as EntryDate,
XMLData.value('(Log/SpecialistID)[1]','int') as SpecialistID,
XMLData.value('(Log/HoursData)[1]','decimal(18,2)') as HoursData,
XMLData.value('(Log/UpdatedBy)[1]','varchar(max)') as UpdatedBy,
XMLData.value('(Log/Options)[1]','varchar(max)') as Options,
logdate
FROM dbo.EditedHourSheetLog
GROUP BY HourSheetID,
XMLData.value('(Log/EntryDate)[1]','datetime'),
XMLData.value('(Log/SpecialistID)[1]','int'),
XMLData.value('(Log/HoursData)[1]','decimal(18,2)'),
XMLData.value('(Log/UpdatedBy)[1]','varchar(max)'),
XMLData.value('(Log/Options)[1]','varchar(max)'),
logdate
如果我无法在 group by 子句中指定 xml 数据,那么还有什么其他选项可用......请指导。谢谢
这样我就完成了我的任务
ALTER PROC sp_HourSheetLog
(
@StartDate VARCHAR(8),
@EndDate VARCHAR(8)
)
AS
SELECT B.ID
,A.RowID
,B.EntryDate
,B.Name
,B.HoursData
,B.UpdatedBy
,Options=(CASE B.Options
WHEN 'rdLeave' THEN 'Leave'
WHEN 'rdsick' THEN 'Sick'
WHEN 'rdSalvage' THEN 'Salvage'
WHEN 'rdCSRDuty' THEN 'CSR Duty'
WHEN 'rdShippingSales' THEN 'Shipping and Sales'
WHEN 'rdEOL' THEN 'EOL'
WHEN 'rdTraining' THEN 'Training'
WHEN 'rdOther' THEN 'Other'
END)
,B.ModDate
FROM (
(
SELECT HourSheetID,(MAX(RowID)+1) as "RowID"
FROM EditedHourSheetLog l,EditedHourSheet h
GROUP BY HourSheetID
) A
JOIN
(
SELECT h.ID
,s.Name
,h.EntryDate
,h.HoursData
,h.Options
,h.UpdatedBy
,h.ModDate from EditedHourSheet h
LEFT JOIN Specialists s
ON h.SpecialistID=s.SpecialistID
) B
ON A.HourSheetID=B.ID
)
WHERE Convert(Varchar,ModDate,112)>=@StartDate AND
Convert(Varchar,ModDate,112)<=@EndDate
UNION
(
Select HourSheetID as ID,RowID,
XMLData.value('(Log/EntryDate)[1]','datetime') as EntryDate,
--XMLData.value('(Log/SpecialistID)[1]','int') as SpecialistID,
s.Name,
XMLData.value('(Log/HoursData)[1]','decimal(18,2)') as HoursData,
CAST(XMLData.value('(Log/UpdatedBy)[1]','varchar(max)') AS VARCHAR(MAX)) UpdatedBy,
Options=(CASE XMLData.value('(Log/Options)[1]','varchar(max)')
WHEN 'rdLeave' THEN 'Leave'
WHEN 'rdsick' THEN 'Sick'
WHEN 'rdSalvage' THEN 'Salvage'
WHEN 'rdCSRDuty' THEN 'CSR Duty'
WHEN 'rdShippingSales' THEN 'Shipping and Sales'
WHEN 'rdEOL' THEN 'EOL'
WHEN 'rdTraining' THEN 'Training'
WHEN 'rdOther' THEN 'Other'
END),
LogDate as ModDate
FROM EditedHourSheetLog h
LEFT JOIN Specialists s
ON h.XMLData.value('(Log/SpecialistID)[1]','int')=s.SpecialistID
WHERE Convert(Varchar,LogDate,112)>=@StartDate AND
Convert(Varchar,LogDate,112)<=@EndDate
)
ORDER BY ID,RowID DESC
--sp_HourSheetLog '20140101','20140326'