一个SQL语句的显示

Question

本帖最后由 wangxiaofeiwuqiao 于 2011-01-12 10:03:53 编辑

Answer 1

数据sqlcreate table 
tbxs(codes varchar(50), names varchar(50),types varchar(20),date datetime,number int)
insert into tbxs values('56569', '塑胶A','原料A','2011-01-05',500)
insert into tbxs values('56569', '塑胶A','原料A','2011-01-06',800)
insert into tbxs values('56569', '塑胶A','原料A','2011-01-11',700)
insert into tbxs values('56569', '塑胶A','原料A','2011-01-01',100)
insert into tbxs values('55558', '塑胶B','原料B','2011-01-05',500)
insert into tbxs values('55558', '塑胶B','原料B','2011-01-09',900)
insert into tbxs values('55558', '塑胶B','原料B','2011-01-02',200)
go

Answer 2

这应该是在程序里控制的，而不是sql语句

Answer 3

我想用sql语句，不想在程序里面实现，不考虑效率问题。

Answer 4

sql查询出的表能达到这个效果？？？
空的位置用什么表示？？  ‘’还是null？？

Answer 5

create table 
tbxs(codes varchar(50), names varchar(50),types varchar(20),date datetime,number int)
insert into tbxs values('56569', '塑胶A','原料A','2011-01-05',500)
insert into tbxs values('56569', '塑胶A','原料A','2011-01-06',800)
insert into tbxs values('56569', '塑胶A','原料A','2011-01-11',700)
insert into tbxs values('56569', '塑胶A','原料A','2011-01-01',100)
insert into tbxs values('55558', '塑胶B','原料B','2011-01-05',500)
insert into tbxs values('55558', '塑胶B','原料B','2011-01-09',900)
insert into tbxs values('55558', '塑胶B','原料B','2011-01-02',200)
gowith cte as
(
select row_number() over (partition by codes order by codes) as ii,* from tbxs
)select
(case ii when 1 then codes end)codes,
(case ii when 1 then names end)names,
(case ii when 1 then types end)types,
date,number
from ctedrop table tbxs

Answer 6

你看这个帖子就知道了，那里是2个表，我这里是一个表，应该更容易些，只是我不会：
http://topic.csdn.net/u/20110110/14/de138652-2881-47a3-9e4b-5502080845b4.html

Answer 7

codes                                              names                                              types                date                    number
-------------------------------------------------- -------------------------------------------------- -------------------- ----------------------- -----------
55558                                              塑胶B                                                原料B                  2011-01-05 00:00:00.000 500
NULL                                               NULL                                               NULL                 2011-01-09 00:00:00.000 900
NULL                                               NULL                                               NULL                 2011-01-02 00:00:00.000 200
56569                                              塑胶A                                                原料A                  2011-01-05 00:00:00.000 500
NULL                                               NULL                                               NULL                 2011-01-06 00:00:00.000 800
NULL                                               NULL                                               NULL                 2011-01-11 00:00:00.000 700
NULL                                               NULL                                               NULL                 2011-01-01 00:00:00.000 100(7 行受影响)

Answer 8

select row_number() over(partition by codes order by date) as num,* into #tb from tbxs
update #tb set codes=null,names=null,types=null where num>1
select * from #tb
drop table #tb

Answer 9

这里能不能不要显示null，我想要“ ”空字符串显示，

Answer 10

declare @table table (id int,name1 varchar(5),name2 varchar(5),date datetime,num int)
insert into @table
select 56569,'塑胶A','原料A','2011-1-5 0:00:00',500 union all
select 56569,'塑胶A','原料A','2011-1-6 0:00:00',800 union all
select 56569,'塑胶A','原料A','2011-1-11 0:00:00',700 union all
select 56569,'塑胶A','原料A','2011-1-1 0:00:00',100 union all
select 55558,'塑胶B','原料B','2011-1-5 0:00:00',500 union all
select 55558,'塑胶B','原料B','2011-1-9 0:00:00',900 union all
select 55558,'塑胶B','原料B','2011-1-2 0:00:00',200select 
id=case when rowid=1 then id else null end,
name1=case when rowid=1 then name1 else null end,
name2=case when rowid=1 then name2 else null end,
date,num from 
(select row_number() over (partition by id,name1,name2 order by id) 
as rowid,* from @table)aa/*
id          name1 name2 date                    num
----------- ----- ----- ----------------------- -----------
55558       塑胶B   原料B   2011-01-05 00:00:00.000 500
NULL        NULL  NULL  2011-01-09 00:00:00.000 900
NULL        NULL  NULL  2011-01-02 00:00:00.000 200
56569       塑胶A   原料A   2011-01-05 00:00:00.000 500
NULL        NULL  NULL  2011-01-06 00:00:00.000 800
NULL        NULL  NULL  2011-01-11 00:00:00.000 700
NULL        NULL  NULL  2011-01-01 00:00:00.000 100
*/

Answer 11

set nocount on
create table 
tbxs(codes varchar(50), names varchar(50),types varchar(20),date datetime,number int)
insert into tbxs values('56569', '塑胶A','原料A','2011-01-05',500)
insert into tbxs values('56569', '塑胶A','原料A','2011-01-06',800)
insert into tbxs values('56569', '塑胶A','原料A','2011-01-11',700)
insert into tbxs values('56569', '塑胶A','原料A','2011-01-01',100)
insert into tbxs values('55558', '塑胶B','原料B','2011-01-05',500)
insert into tbxs values('55558', '塑胶B','原料B','2011-01-09',900)
insert into tbxs values('55558', '塑胶B','原料B','2011-01-02',200)
gowith cte as
(
select row_number() over (partition by codes order by codes) as ii,* from tbxs
)select
(case ii when 1 then codes else '' end)codes,
(case ii when 1 then names else '' end)names,
(case ii when 1 then types else '' end)types,
date,number
from ctedrop table tbxs
set nocount off
codes                                              names                                              types                date                    number
-------------------------------------------------- -------------------------------------------------- -------------------- ----------------------- -----------
55558                                              塑胶B                                                原料B                  2011-01-05 00:00:00.000 500
                                                                                                                           2011-01-09 00:00:00.000 900
                                                                                                                           2011-01-02 00:00:00.000 200
56569                                              塑胶A                                                原料A                  2011-01-05 00:00:00.000 500
                                                                                                                           2011-01-06 00:00:00.000 800
                                                                                                                           2011-01-11 00:00:00.000 700
                                                                                                                           2011-01-01 00:00:00.000 100

Answer 12

declare @table table (id int,name1 varchar(5),name2 varchar(5),date datetime,num int)
insert into @table
select 56569,'塑胶A','原料A','2011-1-5 0:00:00',500 union all
select 56569,'塑胶A','原料A','2011-1-6 0:00:00',800 union all
select 56569,'塑胶A','原料A','2011-1-11 0:00:00',700 union all
select 56569,'塑胶A','原料A','2011-1-1 0:00:00',100 union all
select 55558,'塑胶B','原料B','2011-1-5 0:00:00',500 union all
select 55558,'塑胶B','原料B','2011-1-9 0:00:00',900 union all
select 55558,'塑胶B','原料B','2011-1-2 0:00:00',200select 
id=cast(case when rowid=1 then cast(id as varchar(5)) else '' end as varchar(20)),
name1=cast(case when rowid=1 then name1 else '' end as varchar(20)),
name2=cast(case when rowid=1 then name2 else '' end as varchar(20)),
date,num from 
(select row_number() over (partition by id,name1,name2 order by id) 
as rowid,* from @table)aa/*
id                   name1                name2                date                    num
-------------------- -------------------- -------------------- ----------------------- -----------
55558                塑胶B                  原料B                  2011-01-05 00:00:00.000 500
                                                               2011-01-09 00:00:00.000 900
                                                               2011-01-02 00:00:00.000 200
56569                塑胶A                  原料A                  2011-01-05 00:00:00.000 500
                                                               2011-01-06 00:00:00.000 800
                                                               2011-01-11 00:00:00.000 700
                                                               2011-01-01 00:00:00.000 100
*/