SQL Server 2000，获取 COUNT(DISTINCT ID)，条件是我无法写入 WHERE？
首先，我不想使用“连接”，因为这会使我的查询更长且难以阅读。所以我需要做的必须是使用相同的 SELECT 语句。
myTable 中的列是 A、B、C、D、时间、ID 和 H
H columnd 告诉记录是“打开”还是“关闭”，这里是我的查询的样子。
SELECT
A,
B,
C,
D,
COUNT(DISTINCT ID) AS numberOfRecords,
SUM(time) / COUNT(DISTINCT ID) AS averageTimeOfAllRecords
FROM myTable
WHERE ISNUMERIC(A)=1 AND A IN (SELECT A FROM myTable2)
GROUP BY A,B,C,D
我需要上面的查询返回带有结果的另一列： COUNT(DISTINCT ID) WHERE H = 'Open' 以便我可以获得 numberOfOpenRecords。
我无法将新条件写入“WHERE”，因为这会影响结果，例如 numberOfRecords。
希望我解释了我的问题。
感谢您的帮助。
First of all, I don't want to use a "join" because that will make my query longer and difficult to read. So what I need to do must be withing the same SELECT statement.
My columns in myTable are A, B , C , D , time, ID and H
H columnd tells if a record is 'Open' or 'Close', here how my query looks like.
SELECT
A,
B,
C,
D,
COUNT(DISTINCT ID) AS numberOfRecords,
SUM(time) / COUNT(DISTINCT ID) AS averageTimeOfAllRecords
FROM myTable
WHERE ISNUMERIC(A)=1 AND A IN (SELECT A FROM myTable2)
GROUP BY A,B,C,D
I need the query above to return another column with a result: COUNT(DISTINCT ID) WHERE H = 'Open' so that I can get numberOfOpenRecords.
I can't write my new condition to my "WHERE", because that will effect the results, like numberOfRecords.
Hope I explained my problem.
Thanks for helps.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
由于
count不计算null值，您可以：Since
countdoesn't countnullvalues, you could: