首页 文章

SQL Server查询 - 使用DISTINCT选择COUNT（*）

提问于
浏览 次
327

在SQL Server 2005中，我有一个表cm_production，其中列出了已投入 生产环境 的所有代码 . 该表有ticket_number，program_type，program_name和push_number以及其他一些列 .

目标：按程序类型和推送号计算所有DISTINCT程序名称

到目前为止我所拥有的是：

SELECT DISTINCT COUNT(*) AS Count, program_type AS [Type] 
FROM cm_production 
WHERE push_number=@push_number 
GROUP BY program_type

这让我在那里，但它计算所有的程序名称，而不是不同的程序名称（我不希望它在该查询中做） . 我想我无法绕过如何告诉它只计算不同的程序名而不选择它们 . 或者其他的东西 .

sql sql-server sql-server-2005 tsql

7 回答

  • 25
    select  count (distinct NumTar),'PROPIAS'
from ATM_TRANe with (nolock)
where Fecha>='2014-01-01'
  AND Fecha<='2015-05-31'and NetDestino=0
  and SystemCodResp=0
group by NetDestino 
union 
select sum (contar),'FORANEAS'
from  
(
  select  count(distinct NumTar) as contar
  from ATM_TRANe with (nolock)
  where Fecha>='2014-01-01'
    AND Fecha<='2014-01-31'
    and NetDestino!=0
    and SystemCodResp=0
  group by NetDestino
)dt
    回复于
  • 78

    试试这个：

    SELECT
    COUNT(program_name) AS [Count],program_type AS [Type]
    FROM (SELECT DISTINCT program_name,program_type
              FROM cm_production 
              WHERE push_number=@push_number
         ) dt
    GROUP BY program_type
    回复于
  • 575

    按程序类型和推送编号计算所有DISTINCT程序名称

    SELECT COUNT(DISTINCT program_name) AS Count,
  program_type AS [Type] 
FROM cm_production 
WHERE push_number=@push_number 
GROUP BY program_type

    DISTINCT COUNT（*）将为每个唯一计数返回一行 . 你想要的是COUNT(DISTINCT expression)：计算组中每一行的表达式，并返回唯一的非空值的数量 .

    回复于
  • -1

    我需要得到每个不同值的出现次数 . 该列包含区域信息 . 我最终得到的简单SQL查询是：

    SELECT Region, count(*)
FROM item
WHERE Region is not null
GROUP BY Region

    哪个会给我一个列表，比如说：

    Region, count
Denmark, 4
Sweden, 1
USA, 10
    回复于
  • -5

    您必须为不同的列创建临时表，然后从该表中查询计数

    SELECT COUNT(*) 
FROM (SELECT DISTINCT column1,column2
      FROM  tablename  
      WHERE condition ) as dt

    这里dt是临时表

    回复于
  • 13

    这是一个很好的例子，你想得到存储在最后一个地址字段中的Pincode的数量

    SELECT DISTINCT
    RIGHT (address, 6),
    count(*) AS count
FROM
    datafile
WHERE
    address IS NOT NULL
GROUP BY
    RIGHT (address, 6)
    回复于
  • 12
    SELECT COUNT(DISTINCT program_name) AS Count, program_type AS [Type] 
FROM cm_production 
WHERE push_number=@push_number 
GROUP BY program_type
    回复于

相关问题