我有一个T-SQL查询,我们用于CrystalReport报表。
SELECT COUNT(*) AS Expr1, [Date], StoreNumber
FROM dbo.Orderp
WHERE (OpServerNumber = 0)
GROUP BY [Date], StoreNumber
如果特定日期和商店没有任何有效行,则会出现问题。
如果查询在表中找不到任何行,是否可以返回Expr1 = 0的单行?
我有一个T-SQL查询,我们用于CrystalReport报表。
SELECT COUNT(*) AS Expr1, [Date], StoreNumber
FROM dbo.Orderp
WHERE (OpServerNumber = 0)
GROUP BY [Date], StoreNumber
如果特定日期和商店没有任何有效行,则会出现问题。
如果查询在表中找不到任何行,是否可以返回Expr1 = 0的单行?
IF EXISTS(SELECT COUNT(*) AS Expr1, [Date], StoreNumber FROM dbo.Orderp WHERE (OpServerNumber = 0) GROUP BY [Date], StoreNumber)
SELECT COUNT(*) AS Expr1, [Date], StoreNumber FROM dbo.Orderp WHERE (OpServerNumber = 0) GROUP BY [Date], StoreNumber
ELSE
SELECT 0 AS Expr1, NULL [Date], 0 StoreNumber
SELECT
COUNT(*) AS Expr1, [Date], StoreNumber
FROM dbo.Orderp WHERE (OpServerNumber = 0) GROUP BY [Date], StoreNumber
if @@ROWCOUNT = 0
SELECT 0 AS Expr1, NULL [Date], 0 StoreNumber
select Expr1, [Date], StoreNumber from (
select *,row_number() over (order by isrealrow desc) rownum from (
select COUNT(*) as Expr1,[Date], StoreNumber, 1 as isrealRow FROM dbo.Orderp
WHERE (OpServerNumber = 0)
GROUP BY [Date], StoreNumber
union
select 0, NULL, NULL, 0 as isrealrow
) b
)c
where isrealRow=1 or rownum=1
这是我今天写过的最酷的SQL语句。
SELECT ISNULL(B.num,0) AS Expr1, A.[Date], A.StoreNumber
FROM
(SELECT [Date], StoreNumber FROM dbo.Orderp GROUP BY [Date], StoreNumber) A
LEFT OUTER JOIN
(SELECT COUNT(*) AS num, [Date], StoreNumber
FROM dbo.Orderp
WHERE (OpServerNumber = 0)
GROUP BY
[Date], StoreNumber) B ON A.[Date]=B.[Date] AND A.StoreNumber=B.StoreNumber
编辑:我刚想到另外一个...
SELECT
SUM(Expr1) AS Expr1, [Date], StoreNumber
FROM
(SELECT 0 AS Expr1, [Date], StoreNumber, NULL AS OpServerNumber
FROM dbo.Orderp
GROUP BY [Date], StoreNumber
UNION ALL
SELECT 1, [Date], StoreNumber, OpServerNumber
FROM dbo.Orderp)T
WHERE OpServerNumber IS NULL OR OpServerNumber = 0
GROUP BY [Date], StoreNumber
如果您将此封装在存储过程中,可以执行以下操作:
@@ROWCOUNT
,如果它是 IF @@ROWCOUNT = 0
,则显式地向该临时表添加一个虚拟行SELECT * FROM .....
从临时表中返回内容作为存储过程的结果