表:T1 T2 T3 VT1
| a b a b a b a b
|------------ ------------- ------------ ----------
| a1 10 a1 9 a4 12 a1 9
| a2 6 a2 6 a5 17 a2 6
| a2 4 a3 2 a6 13 a3 2
| a1 23 ... ... a4 12
| a3 4 a5 17
| a2 21 a6 13
| ... ...注:
T2中b只能存放0~9,T3中的b只能存放10~19
VT1是视图(假设为:create view vt1 as select * from t2 union all select * from t3) 问题:
1 怎样得到如下结果 (只用t1表)
1) 只显示 有重复的记录
2) 只显示 无重复的记录
3) 只显示重复记录的第一条,并加一尾列 显示有n条这样的记录
4) 得如下结果(将a中重复的记录只显示一条,把b值累加其后显示)
a b
-----------------
a1 10,23
a2 6,4,21
a3 4
...
2 T2,T3和VT1要怎样建?才能达到这种效果:通过只对 VT1(视图)进行操作(insert),自动将数据操作(insert)入对应的表。如:insert vt1(a,b) values(a7,4)
结果为将插入T2, insert vt1(a,b) values(a8,14) 结果为将插入T3.小弟SQL语句不精,请各道友出手.(sql server)
| a b a b a b a b
|------------ ------------- ------------ ----------
| a1 10 a1 9 a4 12 a1 9
| a2 6 a2 6 a5 17 a2 6
| a2 4 a3 2 a6 13 a3 2
| a1 23 ... ... a4 12
| a3 4 a5 17
| a2 21 a6 13
| ... ...注:
T2中b只能存放0~9,T3中的b只能存放10~19
VT1是视图(假设为:create view vt1 as select * from t2 union all select * from t3) 问题:
1 怎样得到如下结果 (只用t1表)
1) 只显示 有重复的记录
2) 只显示 无重复的记录
3) 只显示重复记录的第一条,并加一尾列 显示有n条这样的记录
4) 得如下结果(将a中重复的记录只显示一条,把b值累加其后显示)
a b
-----------------
a1 10,23
a2 6,4,21
a3 4
...
2 T2,T3和VT1要怎样建?才能达到这种效果:通过只对 VT1(视图)进行操作(insert),自动将数据操作(insert)入对应的表。如:insert vt1(a,b) values(a7,4)
结果为将插入T2, insert vt1(a,b) values(a8,14) 结果为将插入T3.小弟SQL语句不精,请各道友出手.(sql server)
解决方案 »
- 119,急怎么样让ScrollBox控件变成透明?
- 怎样实现数据从一个clientdata送到另一个clientdata中?
- 什么地方可以DownLoad好的电脑书籍
- 数据库高手帮帮忙,看看我这样的想法是否合理?
- 请问如果把DBGrid1里的数据拖放到DBGrid2里该怎么设置DragMode?
- 专门为了结交诸位大虾,菜鸟:)(因为我也是个菜鸟)bow,thanks all
- 关于多表关联问题!急!!!高手请进!在线等
- 同志们谁帮我看看,如果能运行我给加多少分都没有问题
- delphi中怎么让窗口总在前面。
- 怎么拦截系统函数?(超高度难题!)
- 要写一个测试无盘机器的稳定情况,请各位给点意见。
- 怎么用DELPHI代码删除一个只读文件?
insert vt1(a,b) values(a7,4)
insert vt1(a,b) values(a8,14)
改正为:
insert into vt1(a,b) values(a7,4)
insert into vt1(a,b) values(a8,14)
1) 只显示 有重复的记录 <<====是对哪个列??还是所有列都重复?
2) 只显示 无重复的记录
create table t2 (a varchar(10),b int check(b between 0 and 9))
create table t3 (a varchar(10),b int check(b between 10 and 19))
create view vt1 as select * from t2 union all select * from t3
1) 只显示 有重复的记录
select a,b from t1 group by a,b having count(*)>1 2) 只显示 无重复的记录
select a,b from t1 group by a,b having count(*)=1 3) 只显示重复记录的第一条,并加一尾列 显示有n条这样的记录
select a,b,count(*) 数量 from t1 group by a,b having count(*)>1 4) 得如下结果(将a中重复的记录只显示一条,把b值累加其后显示)
create function getstr(@a varchar(10))
returns varchar(1000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+','+cast(b as varchar(10)) from t1 where a=@a
set @str=right(@str,len(@str)-1)
return @str
end
select a,dbo.getstr(a) from t1 group by a
1) 只显示 a列有重复的记录
2) 只显示 a列无重复的记录
3) 只显示a列有重复记录的第一条,并加一尾列 显示有n条这样的记录
4) 得如下结果(将a中重复的记录只显示一条,把b值累加其后显示)
问题2已解决如下:
CREATE TABLE [T1] (
[a] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[b] [int] NOT NULL ,
CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED
(
[a],
[b]
) ON [PRIMARY] ,
CONSTRAINT [CK_T1] CHECK ([b] >= 0 and [b] <= 9)
) ON [PRIMARY]
GOCREATE TABLE [T2] (
[a] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[b] [int] NOT NULL ,
CONSTRAINT [PK_T2] PRIMARY KEY CLUSTERED
(
[a],
[b]
) ON [PRIMARY] ,
CONSTRAINT [CK_T2] CHECK ([b] >= 10 and [b] <= 99)
) ON [PRIMARY]
GOcreate view v1 as select a,b from t1 union all select a,b from t2
FROM t1
WHERE (a IN
(SELECT a
FROM t1
GROUP BY a
HAVING COUNT(*) > 1))
select * from t1 where a in (select a from t1 group by a having count(1)>1)
2) 只显示 a列无重复的记录
select * from t1 where a in (select a from t1 group by a having count(1)=1)
3) 只显示a列有重复记录的第一条,并加一尾列 显示有n条这样的记录
select a from t1 aa where a in (select a from t1 group by a having count(1)>1) and b=(select top 1 b from t1 where a=aa.a)
4) 得如下结果(将a中重复的记录只显示一条,把b值累加其后显示)
create function getstr(@a varchar(10))
returns varchar(1000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+','+cast(b as varchar(10)) from t1 where a=@a
set @str=right(@str,len(@str)-1)
return @str
end
select a,dbo.getstr(a) from t1 group by a