怎么剔除相同记录统计记录数
日期：2014-05-18 浏览次数：20672 次
如何剔除相同记录统计记录数
我有一表mc
id ch
1 wwk-222
2 wwk-222
3 wwk-086
4 wwk-076
5 wwk-089
6 wwk-079
CREATE procedure dwchts
@ch varchar(10)
as
begin
if len(@ch)=1
exec('select count(ch) as ts from mc where SUBSTRING(ch,7,1)='+''''+@ch+'''')
if len(@ch)=2
exec('select count(ch) as ts from mc where SUBSTRING(ch,6,2)='+''''+@ch+'''')
if len(@ch)=3
exec('select count(ch) as ts from mc where SUBSTRING(ch,5,3)='+''''+@ch+'''')
end
GO
执行存储过程是统计6条
我想得到ch完全相同的作1条，应该是5条，如何写
------解决方案--------------------
select count(distinct ch) as ts from mc where。。。。
------解决方案--------------------
我有一表mc
id ch
1 wwk-222
2 wwk-222
3 wwk-086
4 wwk-076
5 wwk-089
6 wwk-079
CREATE procedure dwchts
@ch varchar(10)
as
begin
if len(@ch)=1
exec('select count(ch) as ts from mc where SUBSTRING(ch,7,1)='+''''+@ch+'''')
if len(@ch)=2
exec('select count(ch) as ts from mc where SUBSTRING(ch,6,2)='+''''+@ch+'''')
if len(@ch)=3
exec('select count(ch) as ts from mc where SUBSTRING(ch,5,3)='+''''+@ch+'''')
end
GO
执行存储过程是统计6条
我想得到ch完全相同的作1条，应该是5条，如何写
------解决方案--------------------
select count(distinct ch) as ts from mc where。。。。
------解决方案--------------------
- SQL code
--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test]([id] int,[ch] varchar(7))
insert [test]
select 1,'wwk-222' union all
select 2,'wwk-222' union all
select 3,'wwk-086' union all
select 4,'wwk-076' union all
select 5,'wwk-089' union all
select 6,'wwk-079'
select COUNT(distinct [ch]) as [ch] from test
/*
ch
------
5
*/
--去掉重复使用distinct
------解决方案--------------------
- SQL code
--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test]([id] int,[ch] varchar(7))
insert [test]
select 1,'wwk-222' union all
select 2,'wwk-222' union all
select 3,'wwk-086' union all
select 4,'wwk-076' union all
select 5,'wwk-089' union all
select 6,'wwk-079'
select * from test a
where a.id=(select MIN(b.id) from test b where a.ch=b.ch)
order by id
--相同的ch取最大id的时候把min改成max
/*
id ch
-------------------
1 wwk-222
3 wwk-086
4 wwk-076
5 wwk-089
6 wwk-079
*/
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
