1。
select * from T_Crt where Alias='CNPZ'
TypeID Customer
0 CNFZ
1 CNFZ
4 CNFZ
2。
select * from T_CustomerType
ID LocalName 0 发货人
1 委托人
2 船公司
3 收货人
4 通知人
3。
select CustomerType,Alias from T_Customers where Alias='CNPZ'
CustomerType Alias
|发货人|委托人|通知人 CNPZ
问题:现在客户提供的是第三种表格
|发货人|委托人|通知人 CNPZ
怎么导成第二种表的形式中?????
select * from T_Crt where Alias='CNPZ'
TypeID Customer
0 CNFZ
1 CNFZ
4 CNFZ
2。
select * from T_CustomerType
ID LocalName 0 发货人
1 委托人
2 船公司
3 收货人
4 通知人
3。
select CustomerType,Alias from T_Customers where Alias='CNPZ'
CustomerType Alias
|发货人|委托人|通知人 CNPZ
问题:现在客户提供的是第三种表格
|发货人|委托人|通知人 CNPZ
怎么导成第二种表的形式中?????
用SQL语句处理太麻烦了,好像得用游标。
具体的在http://expert.csdn.net/Expert/topic/1491/1491202.xml?temp=.9056513数据表如下:
甲班姓名 已班姓名 丙班姓名 日期
aa bb cc 2003-2-1
a1 b3 c5 2003-2-1
aa bb c3 2003-2-1
aa b3 c3 2003-2-3
a1 bb cc 2003-2-3
统计甲班、已班、丙班每个人的出现的次数,关键是把统计的次数放到已查寻出的表中,对应于姓名的记录;
我用的是adodataset、dbgrid、并设置adodataset1.commandtext:='select 甲班姓名 count(*) as 甲班工作人数 from dby group by 甲班姓名 union 'select 已班姓名 count(*) as 已班工作人数 from dby group by 已班姓名
可是这样的结果是以列显示,结果还不正确!如何把这样的结果连接到另一个表中,并对应于姓名。
Select 你想要顯示的字段集
From B,
(Select 甲班姓名 as 姓名,count(甲班姓名) as 出現次數 from dby group by 甲班姓名
Union
Select 已班姓名 as 姓名,count(已班姓名) as 出現次數 from dby group by 已班姓名
Union
Select 丙班姓名 as 姓名,count(丙班姓名) as 出現次數 from dby group by 丙班姓名) A
Where A.姓名 = B.姓名
Select ID, LocalName //如果你不要和第二种表合在一起的话
From T_CustomerType B, //去掉这两行
(Select 1 As ID, CustomeTypeas LocalName From T_Customers Where CustomType = '发货人' Group By CustomeType)
union
(Select 2 As ID, CustomeTypeas LocalName From T_Customers Where CustomType = '委托人' Group By CustomeType)
union
(Select 2 As ID, CustomeTypeas LocalName From T_Customers Where CustomType = '通知人' Group By CustomeType)
A //如果你不要和第二种表合在一起的话
Where A.ID = B.ID //去掉这两行 如果答对了的话,别忘了你的承诺哟。:)
Select ID, LocalName //如果你不要和第二种表合在一起的话
From T_CustomerType B, //去掉这两行
(Select 1 As ID, CustomeTypeas LocalName From T_Customers Where CustomType = '发货人' and Alias= 'CNPZ' Group By CustomeType)
union
(Select 2 As ID, CustomeTypeas LocalName From T_Customers Where CustomType = '委托人' and Alias= 'CNPZ' Group By CustomeType)
union
(Select 2 As ID, CustomeTypeas LocalName From T_Customers Where CustomType = '通知人' and Alias= 'CNPZ' Group By CustomeType)
A //如果你不要和第二种表合在一起的话
Where A.ID = B.ID //去掉这两行
Select ID, LocalName //如果你不要和第二种表合在一起的话
From T_CustomerType B, //去掉这两行
(Select 发货人对应的ID As ID, CustomeTypeas LocalName From T_Customers Where CustomType = '发货人' and Alias= 'CNPZ' Group By CustomeType)
union
(Select 委托人对应的ID As ID, CustomeTypeas LocalName From T_Customers Where CustomType = '委托人' and Alias= 'CNPZ' Group By CustomeType)
union
(Select 通知人对应的ID As ID, CustomeTypeas LocalName From T_Customers Where CustomType = '通知人' and Alias= 'CNPZ' Group By CustomeType)
A //如果你不要和第二种表合在一起的话
Where A.ID = B.ID //去掉这两行
其实还有很多的话,也可以像上面一样用Union语句连接。如果是不定的话,还可以采用循环的方式,动态来生成,只不过要采取一定的变通方式。我相信你可以做的到的。
客户提供:表T_Customers
select CustomerType,Alias from T_Customers
CustomerType Alias
|发货人|委托人|通知人 CNPZ
|发货人|委托人 MXB
|发货人|委托人 MDHL
....... .........很多大约500条
现有条件:关系表 T_CustomerType
select * from T_CustomerType
ID LocalName
0 发货人
1 委托人
2 船公司
3 收货人
4 通知人
问题:
怎样导入到表
select * from T_Crt
TypeID Customer
0 CNFZ
1 CNFZ
4 CNFZ
0 MXB
1 MXB
0 MDHL
1 MDHL
Select A.ID, A.Alias From T_CustomerType B,
(Select 0 As ID, CustomeType as LocalName, Alias As Customer From T_Customers Where CustomType = '发货人' Group By CustomeType
union
Select 1 As ID, CustomeType as LocalName, Alias As Customer From T_Customers Where CustomType = '委托人' Group By CustomeType
union
Select 2 As ID, CustomeType as LocalName, Alias As Customer From T_Customers Where CustomType = '船公司' Group By CustomeType
union
Select 3 As ID, CustomeType as LocalName, Alias As Customer From T_Customers Where CustomType = '收货人' Group By CustomeType
union
Select 通知人对应的ID As ID, CustomeType as LocalName,Alias As Customer From T_Customers Where CustomType = '通知人' Group By CustomeType)
A
Where A.ID = B.ID 搞定,如果还有不妥的话,再发短信息。
insert into T_Customers values('|发货人|委托人|通知人', 'CNFZ')
insert into T_Customers values('|发货人|委托人' , 'MXB')
insert into T_Customers values('|发货人|委托人' , 'MDHL')
create table T_CustomerType(ID int, LocalName varchar(100))
insert into T_CustomerType values(0, '发货人')
insert into T_CustomerType values(1, '委托人')
insert into T_CustomerType values(2, '船公司')
insert into T_CustomerType values(3, '收货人')
insert into T_CustomerType values(4, '通知人')
--create table T_Crt
--TypeID Customer
--0 CNFZ
--1 CNFZ
--4 CNFZ
--0 MXB
--1 MXB
--0 MDHL
--1 MDHL
select top 8000 identity(int,1,1) id into #temp from sysobjects a,sysobjects bselect alias,cc.id --substring(customertype,id+1,charindex('|',customertype+'|',id+1)-id-1) as tempName
from T_customers aa,#temp bb ,T_customertype cc
where substring(customertype,bb.id,8000) like '|_%' and cc.localname=substring(customertype,bb.id+1,charindex('|',customertype+'|',bb.id+1)-bb.id-1)
order by alias,bb.iddrop table #temp
以上只是选出来了,要插入你的表中只要在select 前加个insert into T_Crt 就可以了
insert into T_Crt(TypeID,Customer)
select alias,cc.id
from T_customers aa,#temp bb ,T_customertype cc
where substring(customertype,bb.id,8000) like '|_%' and cc.localname=substring(customertype,bb.id+1,charindex('|',customertype+'|',bb.id+1)-bb.id-1)
order by alias,bb.id
insert T_Customers values ('|发货人|委托人|通知人', 'CNPZ')
insert T_Customers values ('|发货人|委托人', 'MXB')
insert T_Customers values ('|发货人|委托人|', 'MDHL')
create table T_CustomerType(ID int, LocalName varchar(10))
insert T_CustomerType values (0, '发货人')
insert T_CustomerType values (1, '委托人')
insert T_CustomerType values (2, '船公司')
insert T_CustomerType values (3, '收货人')
insert T_CustomerType values (4, '通知人') drop table T_crt
select A.id TypeID, B.alias Customer into T_Crt from T_CustomerType as A left join T_Customers as B
on charindex(A.LocalName, B.CustomerType) > 0
where B.alias is not nullselect * from T_Crt
TypeID Customer
----------- ---------
0 CNPZ
1 CNPZ
4 CNPZ
0 MXB
1 MXB
0 MDHL
1 MDHL(7 row(s) affected)
select A.name as col, C.name as type, A.length from
syscolumns A left join sysobjects B
on A.id = B.id left join systypes C
on A.xtype = C.xtype
where B.name = 'a1' or B.name = 'a2'
group by A.name, C.name, A.length
having count(A.name) > 1