sql server 2005table1:
OBJECTID, ProgramVersion, TotalNUM, DataVersion, Notes, CompletionStatustable2:
OBJECTID, SerialNumber, BelongToDep, UseProgramVersion, UseDataVersion, SetupData, Notestable1每条记录中的ProgramVersion和DataVersion两个字段共同约束一个计划,也就是每条记录中的ProgramVersion和DataVersion必有至少一个与其他记录不同table2记录每个设备的UseProgramVersion和UseDataVersion,这两个字段对应table1的ProgramVersion和DataVersion,现在想查询table2中共有多少个不同版本
的UseProgramVersion, UseDataVersion,并且将查询结果和table1连接,将table1的TotalNUM,Notes, CompletionStatus也显示到查询结果中,谢谢!
OBJECTID, ProgramVersion, TotalNUM, DataVersion, Notes, CompletionStatustable2:
OBJECTID, SerialNumber, BelongToDep, UseProgramVersion, UseDataVersion, SetupData, Notestable1每条记录中的ProgramVersion和DataVersion两个字段共同约束一个计划,也就是每条记录中的ProgramVersion和DataVersion必有至少一个与其他记录不同table2记录每个设备的UseProgramVersion和UseDataVersion,这两个字段对应table1的ProgramVersion和DataVersion,现在想查询table2中共有多少个不同版本
的UseProgramVersion, UseDataVersion,并且将查询结果和table1连接,将table1的TotalNUM,Notes, CompletionStatus也显示到查询结果中,谢谢!
解决方案 »
- SQL语句报错:聚合不应出现在 UPDATE 语句的集合列表中。
- 如何最多取出重复记录中的1条?
- 求一个多条件的查询,
- 竟然没人知道?:::两表关联创建视图的问题,简单的,请指教??50分
- 怎么向一个有自动递增字段的表中查数据?
- 仿别人写了一个插入记录触发自动编号,后面发现不会了,请大师们帮以改正!谢谢!
- SQL SERVER数据泄密如何解决?
- 事务日志错误(局域网)
- ms server 2005无法连接到服务器(SERVER NAEE该怎么填啊)
- SqlDataAdapter 查询超时 数据库里面是秒查
- 在sqlserver2005中怎样保存自定义的函数在管理器里
- 2个表合一个表sql如何写啊!
a.TotalNUM,a.Notes, a.CompletionStatus,b.num
from
table1 a
join
(select UseProgramVersion,UseDataVersion,count(1) as num from table2 group by UseProgramVersion,UseDataVersion)b
on
a.ProgramVersion=b.UseProgramVersion and a.DataVersion=b.UseDataVersion
--tryselect a.ProgramVersion,a.Notes,a.TotalNum,a.CompletionStatus,
(select count(distinct UseDataVersion)
from table2
where UseProgramVersion=a.ProgramVersion and UseDataVersion=a.DataVersion
group by UseProgramVersion,UseDataVersion
) VersionCounts
from table1 a
的UseProgramVersion, UseDataVersionUseProgramVersion,UseDataVersion,SerialNumber
1,A,111
1,2,222
2,B,1111
2,B,7777
2,B,8881>相同版本中的个数
select
a.TotalNUM,a.Notes, a.CompletionStatus,b.num
from
table1 a
join
(select UseProgramVersion,UseDataVersion,count(1) as num from table2 group by UseProgramVersion,UseDataVersion)b
on
a.ProgramVersion=b.UseProgramVersion and a.DataVersion=b.UseDataVersion
1,A ,1
1,2 ,1
2,B ,3
我用select distinct UseProgramVersion,UseDataVersion from table2可以得到一半的结果,就是现在正在使用的不同的程序和数据版本共有多少个,如下:
20100311,00000008
20100311,00000009
20100305,00000008
在table1中肯定存在包含以上数据的记录,并且和查询结果一对一
现在table1中要检索出包含以上查询结果的记录,并且显示出来,就像下面这个样子
20100311,00000008,table1.TotalNUM,table1.Notes
20100311,00000009,table1.TotalNUM,table1.Notes
20100305,00000008,table1.TotalNUM,table1.Notes
谢谢!
a.Notes, a.CompletionStatus,a.TotalNUM,num=sum(case when b.UseProgramVersion is null and b.UseDataVersion is null then 1 else 0)
from
table1 a
left join
from table2
on
a.ProgramVersion=b.UseProgramVersion and a.DataVersion=b.UseDataVersion
group by a.ProgramVersion,a.DataVersion,a.Notes, a.CompletionStatus,a.TotalNUM ???
select
a.TotalNUM,a.Notes, a.CompletionStatus,b.num
from
table1 a
join
(select distinct UseProgramVersion,UseDataVersion from table2)b
on
a.ProgramVersion=b.UseProgramVersion and a.DataVersion=b.UseDataVersion
OBJECTID, ProgramVersion, TotalNUM, DataVersion, Notes, CompletionStatus
1,20100311,155,00000008,北京,完成
2,20100311,114,00000009, 上海,完成
3,20100305,211,00000009,深圳,完成table2:
OBJECTID, SerialNumber, BelongToDep, UseProgramVersion, UseDataVersion, SetupData, Notes
1,005,2,20100311,00000008,2010-03-11, 安装
2,006,2,20100311,00000008,2010-03-11, 安装
3,007,13,20100311,000000009,2010-03-11,安装
3,008,14,20100311,000000009,2010-03-11,安装
3,009,13,20100311,000000009,2010-03-11,安装
3,010,13,20100311,000000009,2010-03-11,安装想取的结果,找出表2中的在用版本,然后再表1中找出对应的信息2.UseProgramVersion, 2.UseDataVersion,1.TotalNUM,1.Notes, 1.CompletionStatus
20100311,00000008,155,北京,完成
20100311,000000009,114,上海,完成
===============================================
select
a.*
from
table1 a
join
(select distinct UseProgramVersion,UseDataVersion from table2)b
on
a.ProgramVersion=b.UseProgramVersion and a.DataVersion=b.UseDataVersion
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([OBJECTID] int,[ProgramVersion] datetime,[TotalNUM] int,[DataVersion] varchar(8),[Notes] varchar(4),[CompletionStatus] varchar(4))
insert [table1]
select 1,'20100311',155,'00000008','北京','完成' union all
select 2,'20100311',114,'00000009','上海','完成' union all
select 3,'20100305',211,'00000009','深圳','完成'
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([OBJECTID] int,[SerialNumber] varchar(3),[BelongToDep] int,[UseProgramVersion] datetime,[UseDataVersion] varchar(9),[SetupData] datetime,[Notes] varchar(4))
insert [table2]
select 1,'005',2,'20100311','00000008','2010-03-11','安装' union all
select 2,'006',2,'20100311','00000008','2010-03-11','安装' union all
select 3,'007',13,'20100311','00000009','2010-03-11','安装' union all
select 3,'008',14,'20100311','00000009','2010-03-11','安装' union all
select 3,'009',13,'20100311','00000009','2010-03-11','安装' union all
select 3,'010',13,'20100311','00000009','2010-03-11','安装'
---查询---
select distinct b.UseProgramVersion,b.UseDataVersion,a.TotalNUM,a.Notes, a.CompletionStatus
from table1 a,table2 b
where a.ProgramVersion=b.UseProgramVersion and a.DataVersion=b.UseDataVersion---结果---
UseProgramVersion UseDataVersion TotalNUM Notes CompletionStatus
------------------------------------------------------ -------------- ----------- ----- ----------------
2010-03-11 00:00:00.000 00000008 155 北京 完成
2010-03-11 00:00:00.000 00000009 114 上海 完成(所影响的行数为 2 行)
insert into table1 values(1,'20100311',155,'00000008','北京','完成')
insert into table1 values(2,'20100311',114,'00000009','上海','完成')
insert into table1 values(3,'20100305',211,'00000009','深圳','完成')
create table table2(OBJECTID int, SerialNumber varchar(10), BelongToDep int, UseProgramVersion varchar(10), UseDataVersion varchar(10), SetupData datetime, Notes varchar(10))
insert into table2 values(1,'005',2 ,'20100311','00000008','2010-03-11','安装')
insert into table2 values(2,'006',2 ,'20100311','00000008','2010-03-11','安装')
insert into table2 values(3,'007',13,'20100311','00000009','2010-03-11','安装')
insert into table2 values(3,'008',14,'20100311','00000009','2010-03-11','安装')
insert into table2 values(3,'009',13,'20100311','00000009','2010-03-11','安装')
insert into table2 values(3,'010',13,'20100311','00000009','2010-03-11','安装')
goselect b.UseProgramVersion,b.UseDataVersion,count(1) , a.TotalNUM,a.Notes,a.CompletionStatus
from table2 b , table1 a
where b.UseProgramVersion = a.ProgramVersion and b.UseDataVersion = a.DataVersion
group by b.UseProgramVersion,b.UseDataVersion,a.TotalNUM,a.Notes,a.CompletionStatusdrop table table1 , table2/*
UseProgramVersion UseDataVersion TotalNUM Notes CompletionStatus
----------------- -------------- ----------- ----------- ---------- ----------------
20100311 00000008 2 155 北京 完成
20100311 00000009 4 114 上海 完成(所影响的行数为 2 行)
*/
from table1 a inner join table2 b
on a.ProgramVersion=b.UseProgramVersion and a.DataVersion=b.UseDataVersion
---测试数据---
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([OBJECTID] int,[ProgramVersion] datetime,[TotalNUM] int,[DataVersion] varchar(8),[Notes] varchar(4),[CompletionStatus] varchar(4))
insert [table1]
select 1,'20100311',155,'00000008','北京','完成' union all
select 2,'20100311',114,'00000009','上海','完成' union all
select 3,'20100305',211,'00000009','深圳','完成'
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([OBJECTID] int,[SerialNumber] varchar(3),[BelongToDep] int,[UseProgramVersion] datetime,[UseDataVersion] varchar(9),[SetupData] datetime,[Notes] varchar(4))
insert [table2]
select 1,'005',2,'20100311','00000008','2010-03-11','安装' union all
select 2,'006',2,'20100311','00000008','2010-03-11','安装' union all
select 3,'007',13,'20100311','00000009','2010-03-11','安装' union all
select 3,'008',14,'20100311','00000009','2010-03-11','安装' union all
select 3,'009',13,'20100311','00000009','2010-03-11','安装' union all
select 3,'010',13,'20100311','00000009','2010-03-11','安装'select
a.*
from
table1 a
join
(select distinct UseProgramVersion,UseDataVersion from table2)b
on
a.ProgramVersion=b.UseProgramVersion and a.DataVersion=b.UseDataVersion/*OBJECTID ProgramVersion TotalNUM DataVersion Notes CompletionStatus
----------- ------------------------------------------------------ ----------- ----------- ----- ----------------
1 2010-03-11 00:00:00.000 155 00000008 北京 完成
2 2010-03-11 00:00:00.000 114 00000009 上海 完成(所影响的行数为 2 行)
*/
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([OBJECTID] int,[ProgramVersion] datetime,[TotalNUM] int,[DataVersion] varchar(8),[Notes] varchar(4),[CompletionStatus] varchar(4))
insert [table1]
select 1,'20100311',155,'00000008','北京','完成' union all
select 2,'20100311',114,'00000009','上海','完成' union all
select 3,'20100305',211,'00000009','深圳','完成'
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([OBJECTID] int,[SerialNumber] varchar(3),[BelongToDep] int,[UseProgramVersion] datetime,[UseDataVersion] varchar(9),[SetupData] datetime,[Notes] varchar(4))
insert [table2]
select 1,'005',2,'20100311','00000008','2010-03-11','安装' union all
select 2,'006',2,'20100311','00000008','2010-03-11','安装' union all
select 3,'007',13,'20100311','00000009','2010-03-11','安装' union all
select 3,'008',14,'20100311','00000009','2010-03-11','安装' union all
select 3,'009',13,'20100311','00000009','2010-03-11','安装' union all
select 3,'010',13,'20100311','00000009','2010-03-11','安装'select
a. ProgramVersion,a.TotalNUM,a.DataVersion,a.Notes,a.CompletionStatus
from
table1 a
join
(select distinct UseProgramVersion,UseDataVersion from table2)b
on
a.ProgramVersion=b.UseProgramVersion and a.DataVersion=b.UseDataVersion/*ProgramVersion TotalNUM DataVersion Notes CompletionStatus
------------------------------------------------------ ----------- ----------- ----- ----------------
2010-03-11 00:00:00.000 155 00000008 北京 完成
2010-03-11 00:00:00.000 114 00000009 上海 完成(所影响的行数为 2 行)*/