我需要按某几个字段Order By显示。与此同时,所显示的众多字段中必有三个字段有重复的值。我要达到的效果的相同的字段中第一个记录照常显示,下面几个相同值的字段值显示NULL。例如
ID 姓名 性别 班级 月份 成绩
09 张三 男 2 5 94
09 NULL NULL NULL 6 85
09 NULL MULL NULL 7 93
14 李四 女 2 5 97
14 NULL NULL NULL 6 92
谢谢!
ID 姓名 性别 班级 月份 成绩
09 张三 男 2 5 94
09 NULL NULL NULL 6 85
09 NULL MULL NULL 7 93
14 李四 女 2 5 97
14 NULL NULL NULL 6 92
谢谢!
解决方案 »
- 求一SQL分类查询语句,急急急在线等,解决立即给分!
- 存储图片:直接存为image数据类型还是存为text类型(图片路径)?
- CLSID {10020200-E260-11CF-AE68-00AA004A34D5} 的 COM 对象无效或未注册
- 求教一sql,关于修改多行nvarchar类型数据
- 数据库结构设计的一个疑问:当用两个字段索引时,别的表是否也拿这两个键做为referenceId
- 两语句性能对比
- 高手请进。。 进入有分
- 请问sql server中建表出错后不能再建此表,怎么办?
- 求解数据库count()用法
- 一个表的字段值按另一个表的值进行更新
- 帮忙写个查询语句,测试数据已经写好,谢谢
- datediff 获取数据精确到秒
--======= 每天都在进步,却依然追不上地球的自传=========
--======= By: zc_0101 At:2010-07-23 10:42:20=========
--========++++++++++++++++++++++++++++++++++++=========
--> 测试数据: #T1
if object_id('tempdb.dbo.#T1') is not null drop table #T1
create table #T1 (col1 varchar(2),col2 varchar(4),col3 varchar(2),col4 int,col5 int,col6 int)
insert into #T1
select '09','张三','男',2,5,94 union all
select '09','张三','男',2,6,85 union all
select '09','张三','男',2,7,93 union all
select '14','李四','女',2,5,97 union all
select '14','李四','女',2,6,92select * from #T1
----------------查询------------
WITH ZC AS(
SELECT
RID_COL2=ROW_NUMBER() OVER (PARTITION BY COL2 ORDER BY COL1,COL2,COL3,COL4,COL5,COL6),
RID_COL3=ROW_NUMBER() OVER (PARTITION BY COL3 ORDER BY COL1,COL2,COL3,COL4,COL5,COL6),
RID_COL4=ROW_NUMBER() OVER (PARTITION BY COL4 ORDER BY COL1,COL2,COL3,COL4,COL5,COL6),
RID_COL5=ROW_NUMBER() OVER (PARTITION BY COL5 ORDER BY COL1,COL2,COL3,COL4,COL5,COL6),
RID_COL6=ROW_NUMBER() OVER (PARTITION BY COL6 ORDER BY COL1,COL2,COL3,COL4,COL5,COL6),
*
FROM #T1
)
SELECT COL1,
CASE RID_COL2 WHEN 1 THEN COL2 ELSE NULL END AS COL2,
CASE RID_COL3 WHEN 1 THEN COL3 ELSE NULL END AS COL3,
CASE RID_COL4 WHEN 1 THEN COL4 ELSE NULL END AS COL4,
CASE RID_COL5 WHEN 1 THEN COL5 ELSE NULL END AS COL5,
CASE RID_COL6 WHEN 1 THEN COL6 ELSE NULL END AS COL6
FROM ZC
ORDER BY COL1,RID_COL2,RID_COL3,RID_COL4,RID_COL5,RID_COL6
----------------结果--------------
/*
COL1 COL2 COL3 COL4 COL5 COL6
09 张三 男 2 5 94
09 NULL NULL NULL 6 85
09 NULL NULL NULL 7 93
14 李四 女 NULL NULL 97
14 NULL NULL NULL NULL 92
*/
--========+++++++++++++++++++++++++++++++++++==========
--======= 每天都在进步,却依然追不上地球的自传=========
--======= By: zc_0101 At:2010-07-23 10:42:20=========
--========++++++++++++++++++++++++++++++++++++=========
--> 测试数据: #T1
if object_id('tempdb.dbo.#T1') is not null drop table #T1
create table #T1 (col1 varchar(2),col2 varchar(4),col3 varchar(2),col4 int,col5 int,col6 int)
insert into #T1
select '09','张三','男',2,5,94 union all
select '09','张三','男',2,6,85 union all
select '09','张三','男',2,7,93 union all
select '14','李四','女',2,5,97 union all
select '14','李四','女',2,6,92select * from #T1
----------------查询------------
;WITH ZC AS(
SELECT
RID_COL2=ROW_NUMBER() OVER (PARTITION BY COL2 ORDER BY COL1,COL2,COL3,COL4,COL5,COL6),
RID_COL3=ROW_NUMBER() OVER (PARTITION BY COL2,COL3 ORDER BY COL1,COL2,COL3,COL4,COL5,COL6),
RID_COL4=ROW_NUMBER() OVER (PARTITION BY COL2,COL3,COL4 ORDER BY COL1,COL2,COL3,COL4,COL5,COL6),
RID_COL5=ROW_NUMBER() OVER (PARTITION BY COL2,COL3,COL4,COL5 ORDER BY COL1,COL2,COL3,COL4,COL5,COL6),
RID_COL6=ROW_NUMBER() OVER (PARTITION BY COL2,COL3,COL4,COL5,COL6 ORDER BY COL1,COL2,COL3,COL4,COL5,COL6),
*
FROM #T1
)
SELECT COL1,
CASE RID_COL2 WHEN 1 THEN COL2 ELSE NULL END AS COL2,
CASE RID_COL3 WHEN 1 THEN COL3 ELSE NULL END AS COL3,
CASE RID_COL4 WHEN 1 THEN COL4 ELSE NULL END AS COL4,
CASE RID_COL5 WHEN 1 THEN COL5 ELSE NULL END AS COL5,
CASE RID_COL6 WHEN 1 THEN COL6 ELSE NULL END AS COL6
FROM ZC
ORDER BY COL1,RID_COL2,RID_COL3,RID_COL4,RID_COL5,RID_COL6
----------------结果--------------
/*(5 行受影响)
col1 col2 col3 col4 col5 col6
---- ---- ---- ----------- ----------- -----------
09 张三 男 2 5 94
09 张三 男 2 6 85
09 张三 男 2 7 93
14 李四 女 2 5 97
14 李四 女 2 6 92(5 行受影响)COL1 COL2 COL3 COL4 COL5 COL6
---- ---- ---- ----------- ----------- -----------
09 张三 男 2 5 94
09 NULL NULL NULL 6 85
09 NULL NULL NULL 7 93
14 李四 女 2 5 97
14 NULL NULL NULL 6 92(5 行受影响)
*/
case rownum when 1 then 姓名 else null end as '姓名,
case rownum when 1 then 性别 else null end as '性别,
case rownum when 1 then 班级 else null end as '班级,
t.月份,t.成绩
from(
select row_number() over (partition by 料号 order by 姓名) as 'rownum',* from table as t
select t.rownum,
case rownum when 1 then 姓名 else null end as '姓名',
case rownum when 1 then 性别 else null end as '性别',
case rownum when 1 then 班级 else null end as '班级',
t.月份,t.成绩
from(
select row_number() over (partition by 料号 order by 姓名) as 'rownum',* from table as t
case rownum when 1 then 姓名 else null end as '姓名',
case rownum when 1 then 性别 else null end as '性别',
case rownum when 1 then 班级 else null end as '班级',
t.月份,t.成绩
from(
select row_number() over (partition by 料号 order by 姓名) as 'rownum',* from table) as t--少了一括号
与我的需求略有出入。如果姓名、性别和班级三个字段不完全一样,就重新刷新显示出来,不要NULL一贯到底。--- 修改了下
select t.rownum,
case when rownum=1 then t.姓名
else null end as '姓名',
case when rownum=1 then t.性别
when rownum<>1 and t.性别 not in (select distinct 性别 from (select row_number() over (partition by col1 order by col1) as 'rownum',* from 'table') as t where t.rownum=1) then t.性别
else null end as '性别',
case when rownum=1 then t.班级
when rownum<>1 and t.班级 not in (select distinct 班级 from (select row_number() over (partition by col1 order by col1) as 'rownum',* from 'table') as t where t.rownum=1) then t.col3
else null end as '班级',
t.col4,t.col5,t.col6
from(select row_number() over (partition by col1 order by col1) as 'rownum',* from 'table') as t