m*2
2 楼
SQL server 2005
运行下面的query:
SELECT p.[Code],
COUNT(*) AS leads,
SUM(CASE WHEN p.[Stage] ='ENR' THEN 1 ELSE 0 END) AS ENR1,
SUM (CASE WHEN (p.[Stage]='ENR' AND p.[ID] IN (
SELECT b.[ID] FROM tbl2 b)) then 1 else 0 end) AS ENR2 FROM tbl1 p
WHERE p.[Code] IN
(xxxx)
GROUP BY p.[Code]
ORDER BY p.[Code]
然后得到一个error message:
Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an
aggregate or a subquery
google了一下这个message,说是:
you cannot use subquery when
运行下面的query:
SELECT p.[Code],
COUNT(*) AS leads,
SUM(CASE WHEN p.[Stage] ='ENR' THEN 1 ELSE 0 END) AS ENR1,
SUM (CASE WHEN (p.[Stage]='ENR' AND p.[ID] IN (
SELECT b.[ID] FROM tbl2 b)) then 1 else 0 end) AS ENR2 FROM tbl1 p
WHERE p.[Code] IN
(xxxx)
GROUP BY p.[Code]
ORDER BY p.[Code]
然后得到一个error message:
Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an
aggregate or a subquery
google了一下这个message,说是:
you cannot use subquery when
b*t
3 楼
太厉害了!喜欢这个!
c*t
4 楼
join tbl1 and tbl2 first, then sum
【在 m**********2 的大作中提到】
: SQL server 2005
: 运行下面的query:
: SELECT p.[Code],
: COUNT(*) AS leads,
: SUM(CASE WHEN p.[Stage] ='ENR' THEN 1 ELSE 0 END) AS ENR1,
: SUM (CASE WHEN (p.[Stage]='ENR' AND p.[ID] IN (
: SELECT b.[ID] FROM tbl2 b)) then 1 else 0 end) AS ENR2 FROM tbl1 p
: WHERE p.[Code] IN
: (xxxx)
: GROUP BY p.[Code]
【在 m**********2 的大作中提到】
: SQL server 2005
: 运行下面的query:
: SELECT p.[Code],
: COUNT(*) AS leads,
: SUM(CASE WHEN p.[Stage] ='ENR' THEN 1 ELSE 0 END) AS ENR1,
: SUM (CASE WHEN (p.[Stage]='ENR' AND p.[ID] IN (
: SELECT b.[ID] FROM tbl2 b)) then 1 else 0 end) AS ENR2 FROM tbl1 p
: WHERE p.[Code] IN
: (xxxx)
: GROUP BY p.[Code]
h*q
5 楼
我爸爸明年七十大寿,我妈特意绣的,另一个百福说是要给我。
她说字比画好绣。
她说字比画好绣。
B*g
8 楼
"you cannot use subquery when you use SUM function. Use Join and filter the
records。"
人家不是说了用join吗?
SELECT p.[Code],
COUNT(*) AS leads,
SUM(CASE WHEN p.[Stage] ='ENR' THEN 1 ELSE 0 END) AS ENR1,
SUM(CASE WHEN p.[Stage] ='ENR' AND b.[ID] IS NOT NULL THEN 1 ELSE 0
END) AS ENR2
FROM tbl1 p LEFT JOIN tbl2 b ON p.[ID] = b.[ID]
GROUP BY p.[Code]
【在 m**********2 的大作中提到】
: SQL server 2005
: 运行下面的query:
: SELECT p.[Code],
: COUNT(*) AS leads,
: SUM(CASE WHEN p.[Stage] ='ENR' THEN 1 ELSE 0 END) AS ENR1,
: SUM (CASE WHEN (p.[Stage]='ENR' AND p.[ID] IN (
: SELECT b.[ID] FROM tbl2 b)) then 1 else 0 end) AS ENR2 FROM tbl1 p
: WHERE p.[Code] IN
: (xxxx)
: GROUP BY p.[Code]
records。"
人家不是说了用join吗?
SELECT p.[Code],
COUNT(*) AS leads,
SUM(CASE WHEN p.[Stage] ='ENR' THEN 1 ELSE 0 END) AS ENR1,
SUM(CASE WHEN p.[Stage] ='ENR' AND b.[ID] IS NOT NULL THEN 1 ELSE 0
END) AS ENR2
FROM tbl1 p LEFT JOIN tbl2 b ON p.[ID] = b.[ID]
GROUP BY p.[Code]
【在 m**********2 的大作中提到】
: SQL server 2005
: 运行下面的query:
: SELECT p.[Code],
: COUNT(*) AS leads,
: SUM(CASE WHEN p.[Stage] ='ENR' THEN 1 ELSE 0 END) AS ENR1,
: SUM (CASE WHEN (p.[Stage]='ENR' AND p.[ID] IN (
: SELECT b.[ID] FROM tbl2 b)) then 1 else 0 end) AS ENR2 FROM tbl1 p
: WHERE p.[Code] IN
: (xxxx)
: GROUP BY p.[Code]
h*q
11 楼
全光谱的灯
what's the English name for it?
I want to buy one for my mom.
she's coming this month.
what's the English name for it?
I want to buy one for my mom.
she's coming this month.
B*g
12 楼
http://www.w3schools.com/Sql/sql_join_left.asp
【在 m**********2 的大作中提到】
: 但是join的话,
: COUNT(*) AS leads,
: SUM(CASE WHEN p.[Stage] ='ENR' THEN 1 ELSE 0 END) AS ENR1
: 这两个数就不对了,这两个只需要计tbl1里的。。。
【在 m**********2 的大作中提到】
: 但是join的话,
: COUNT(*) AS leads,
: SUM(CASE WHEN p.[Stage] ='ENR' THEN 1 ELSE 0 END) AS ENR1
: 这两个数就不对了,这两个只需要计tbl1里的。。。
相关阅读
大量免费Oracle培训视频,帮你自学成才怎样 show existing index in SQL Server?how to extract large number of rows from sql server?代发一个招工启事吐血了小问题trigger vs. log ?Make a full backup every hour. Is it crazy?面试回来发考题2Help! A cluster method in SQLsql server 面试题 (10)节日有奖如何展开properties table?请教如何将teradata的数据导入oracle?Donated 88.88 (new cash) to database board.sql server 面试题 (9)sqlplus vs ODP.Net如何在数据库中进行复杂查询, 但不把中间结果放到程序内存how to display a variable适用于sql server 和oracle 的 sql batch?谁知道这个公司Artech Information Systems LLC (转载)