无法在Microsoft SQL Server中运行查询

我在运行/配置Microsoft SQL Server查询时遇到问题,查询如下:

SELECT
    ps.WeekIncluded AS PaidWeeks,
    PayD.PayDate AS PayDates,
    ps.PayYear AS PYear, 
    months.PMonth,
    (SELECT sum(DayPay) FROM Shifts GROUP BY WeekNumber)
FROM dbo.PayStructure ps
JOIN dbo.Months Months 
ON ps.MonthID = months.ID
JOIN dbo.PayDates PayD ON ps.MonthID = PayD.MonthID
Group BY ps.MonthID

这是尝试做的,是使用三个表创建一个视图(不包含在代码段中),包括选择Shifts和GroupBy中的DayPay总和,以便稍后加入并按周数连接到指定月份的周数 . 不幸的是我得到了:

列'dbo.PayStructure.WeekIncluded'在选择列表中无效,因为它不包含在聚合函数或GROUP BY子句中 .

使用:

SELECT
    ps.WeekIncluded AS PaidWeeks,
    PayD.PayDate AS PayDates,
    ps.PayYear AS PYear, 
    months.PMonth,
    (SELECT sum(DayPay) FROM Shifts)
FROM dbo.PayStructure ps
JOIN dbo.Months Months 
ON ps.MonthID = months.ID
JOIN dbo.PayDates PayD ON ps.MonthID = PayD.MonthID

返回:

Sucessful Query, Wrong results

每个被选中的等于:60.36我想要得到的是:

Janurary - Null二月 - Null March - Null April - Null May - Null June - Null July - Null August - Null September - 10月60.36 - Null December - Null

由于没有数据/周的数据输入,因此无效为11/12个月


如被问及 . Dbo.Shifts
Dbo.shifts

Dbo.PayStructure
dbo.PayStructure

Dbo.Months
Dbo.Months


尝试:

SELECT
     ps.WeekIncluded AS PaidWeeks, 
     PayD.PayDate AS PayDates,
     ps.PayYear AS PYear,
       SUM(Sh.DayPay) 
FROM   dbo.PayStructure ps
       LEFT JOIN dbo.Months Months 
         ON ps.MonthID = months.ID
       LEFT JOIN dbo.PayDates PayD
         ON ps.MonthID = PayD.MonthID
        LEFT JOIN dbo.Shifts Sh
         ON Sh.WeekNumber = ps.WeekIncluded
GROUP  BY Sh.WeekNumber

回答(3)

2 years ago

SELECT
ps.WeekIncluded AS PaidWeeks,
PayD.PayDate AS PayDates,
ps.PayYear AS PYear, 
months.PMonth,
SumPay
FROM dbo.PayStructure ps
JOIN dbo.Months Months 
ON ps.MonthID = months.ID
JOIN dbo.PayDates PayD ON ps.MonthID = PayD.MonthID
LEFT JOIN 
(SELECT S. WeekNumber,
            PSI.MonthId,
            PSI.PayYear,
            sum(DayPay) AS SumPay
FROM Shifts S
JOIN dbo.PayStrycture PSI
ON S.WeekNumber = PSI.WeekIncluded
AND YEAR(S.ShiftDate) = PSI.PayYear
JOIN dbo.Months M
ON PSI.MonthId = M.ID
GROUP BY S. WeekNumber,
            PSI.MonthId,
            PSI.PayYear
) T
ON PS.MonthId = T.MonthId
AND PS.PayYear = T.PayYear
AND PS.WeekIncluded = T.WeekNumber

2 years ago

错误信息相当清楚; SQL Server无法从您的查询中知道 WeekIncluded 的哪个可能值应该包含您查询的结果集 . 您可以自己考虑一下 - 输出中每月会有一行,而WeekIncluded值会有一行"slot" . 应该显示哪一个?

尝试运行这样的查询:

SELECT
    MIN(ps.WeekIncluded) AS FromWeek,
    MAX(ps.WeekIncluded) AS ToWeek,
    . . .

你应该更好地了解发生了什么 .

其他一些引擎包括一个名为 group_concat 的额外聚合函数来处理这种查询,但是SQL Server很难在这样的聚合中汇总以逗号分隔的值列表 .

2 years ago

任何不是聚合的SELECTed列(例如SUM()或COUNT())都必须包含在GroupBy中,否则查询将不会执行 .

所以:

SELECT
ps.WeekIncluded AS PaidWeeks,
PayD.PayDate AS PayDates,
ps.PayYear AS PYear, 
months.PMonth,
(SELECT sum(DayPay) FROM Shifts GROUP BY WeekNumber)
FROM dbo.PayStructure ps
JOIN dbo.Months Months 
ON ps.MonthID = months.ID
JOIN dbo.PayDates PayD ON ps.MonthID = PayD.MonthID
Group BY ps.MonthID,ps.WeekIncluded,PayD.PayDate,months.PMonth