数据:7846 36 1
7958 36 1
7958 20 2
7958 5 1
7958 38 10
7958 37 2
7963 20 1
7982 23 2要得到的结果7846 36 1 1
7958 36 1 1
7958 20 2 0
7958 5 1 0
7958 38 10 0
7958 37 2 0
7963 20 1 1
7982 23 2 1
也就是第一列如果相同,我只统计一次。用SQL怎么写呢?
7958 36 1
7958 20 2
7958 5 1
7958 38 10
7958 37 2
7963 20 1
7982 23 2要得到的结果7846 36 1 1
7958 36 1 1
7958 20 2 0
7958 5 1 0
7958 38 10 0
7958 37 2 0
7963 20 1 1
7982 23 2 1
也就是第一列如果相同,我只统计一次。用SQL怎么写呢?
go
create table [tb]([col1] int,[col2] int,[col3] int)
insert [tb]
select 7846,36,1 union all
select 7958,36,1 union all
select 7958,20,2 union all
select 7958,5,1 union all
select 7958,38,10 union all
select 7958,37,2 union all
select 7963,20,1 union all
select 7982,23,2select col1,col2,col2,
col4=case
when not exists(select 1 from (select *,rn=row_number() over(order by getdate()) from tb) b where a.col1=b.col1 and a.rn>b.rn)
then 1
else 0
end
from
(
select *,rn=row_number() over(order by getdate()) from tb
) a/**
col1 col2 col2 col4
----------- ----------- ----------- -----------
7846 36 36 1
7958 36 36 1
7958 20 20 0
7958 5 5 0
7958 38 38 0
7958 37 37 0
7963 20 20 1
7982 23 23 1(8 行受影响)
**/
col4=case
when not exists(select 1 from (select *,rn=row_number() over(order by getdate()) from tb) b where a.col1=b.col1 and a.rn>b.rn)
then 1
else 0
end
from
(
select *,rn=row_number() over(order by getdate()) from tb
) a/**
col1 col2 col3 col4
----------- ----------- ----------- -----------
7846 36 1 1
7958 36 1 1
7958 20 2 0
7958 5 1 0
7958 38 10 0
7958 37 2 0
7963 20 1 1
7982 23 2 1(8 行受影响)
**/
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0 C Language
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server 2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
col1 int,
col2 int,
col3 int
)
go
--插入测试数据
insert into tb select 7846,36,1
union all select 7958,36,1
union all select 7958,20,2
union all select 7958,5,1
union all select 7958,38,10
union all select 7958,37,2
union all select 7963,20,1
union all select 7982,23,2
go
--代码实现
;with t as(select id=row_number()over(order by getdate()),* from tb)
select a.col1,a.col2,a.col3,case when a.col1=b.col1 then 0 else 1 end col4
from t a left join t b on a.id=b.id+1
/*测试结果col1 col2 col3 col4
---------------------
7846 36 1 1
7958 36 1 1
7958 20 2 0
7958 5 1 0
7958 38 10 0
7958 37 2 0
7963 20 1 1
7982 23 2 1(8 行受影响)
*/
DROP TABLE TB
go
CREATE TABLE TB (a INT,b INT,c int)
go
INSERT TB SELECT
7846, 36 ,1 UNION SELECT
7958, 36 ,1 UNION SELECT
7958, 20 ,2 UNION SELECT
7958, 5 ,1 UNION SELECT
7958, 38, 10 UNION SELECT
7958, 37 ,2 UNION SELECT
7963, 20, 1 UNION SELECT
7982, 23, 2
goSELECT t2.a,t2.b,t2.c ,
CASE WHEN t2.a = ( SELECT TOP 1
a
FROM ( SELECT * ,
ROW_NUMBER() OVER ( ORDER BY a DESC ) rn
FROM tb
) t1
WHERE t1.rn = t2.rn - 1
) THEN 0
ELSE 1
END AS d
FROM ( SELECT * ,
ROW_NUMBER() OVER ( ORDER BY a DESC ) rn
FROM tb
) t2
a b c d
----------- ----------- ----------- -----------
7982 23 2 1
7963 20 1 1
7958 5 1 1
7958 20 2 0
7958 36 1 0
7958 37 2 0
7958 38 10 0
7846 36 1 1(所影响的行数为 8 行)
DROP TABLE TB
go
CREATE TABLE TB (a INT,b INT,c int)
go
INSERT TB SELECT
7846, 36 ,1 UNION SELECT
7958, 36 ,1 UNION SELECT
7958, 20 ,2 UNION SELECT
7958, 5 ,1 UNION SELECT
7958, 38, 10 UNION SELECT
7958, 37 ,2 UNION SELECT
7963, 20, 1 UNION SELECT
7982, 23, 2
goselect *,
case when row_number() over(partition by a order by (select 1))=1 then 1 else 0 end as cnt from TB
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-20 13:37:04
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col1] int,[col2] int,[col3] int)
insert [tb]
select 7846,36,1 union all
select 7958,36,1 union all
select 7958,20,2 union all
select 7958,5,1 union all
select 7958,38,10 union all
select 7958,37,2 union all
select 7963,20,1 union all
select 7982,23,2
--------------开始查询--------------------------
select
*,
col4=case id when 1 then 1 else 0 end
from
(select *,id=row_number()over(partition by col1 order by getdate()) from tb)t
----------------结果----------------------------
/* col1 col2 col3 id col4
----------- ----------- ----------- -------------------- -----------
7846 36 1 1 1
7958 36 1 1 1
7958 20 2 2 0
7958 5 1 3 0
7958 38 10 4 0
7958 37 2 5 0
7963 20 1 1 1
7982 23 2 1 1(8 行受影响)*/
-- Author : htl258(Tony)
-- Date : 2010-04-20 13:55:38
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([a] INT,[b] INT,[c] INT)
INSERT [tb]
SELECT 7846,36,1 UNION ALL
SELECT 7958,36,1 UNION ALL
SELECT 7958,20,2 UNION ALL
SELECT 7958,5,1 UNION ALL
SELECT 7958,38,10 UNION ALL
SELECT 7958,37,2 UNION ALL
SELECT 7963,20,1 UNION ALL
SELECT 7982,23,2
GO
--SELECT * FROM [tb]-->SQL查询如下:
select flag=identity(int),* into # from tb order by aselect a,b,c,
d=case when (select COUNT(1) from #
where a=t.a and flag<=t.flag)=1 then 1 else 0
end
from # t
/*
a b c d
----------- ----------- ----------- -----------
7846 36 1 1
7958 36 1 1
7958 20 2 0
7958 5 1 0
7958 38 10 0
7958 37 2 0
7963 20 1 1
7982 23 2 1(8 行受影响)
*/drop table #