求助:三个表之间复杂关联,并且涉及到if语句的sql语句~~~ 如果A表中的OrgUnitTypeRoleId 值为1的话,C表将对应有6条记录,PermissionId分别是“100001”,“100002”......"100007",如果A表中的OrgUnitTypeRoleId 值为2的话,C表对应的记录将不同。----------------为2 是什么情况呢,看不出规律 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 是2的话,PermissionId将是“100003”,“100004”也就是说增加两条记录 感觉这个不能用sql语句实现吧?太复杂了,我都有点晕了,难道是快要下班的缘故? 还是看不明白你说的是什么意思1的时候是这样 20,34,100001 20,34,100002 20,34,100003 20,34,100004 20,34,100005 20,34,100006是2的话,PermissionId将是“100003”,“100004”也就是说增加两条记录那是什么结果???????写出来 你说不规律,那我就写一个不能用的declare @UnitUser table(UserID int,OrgUnitID int,OrgUnitTypeRoleId int)declare @SubscriptionOrgs table(SubscriptionId int,OrgUnitId int)insert @UnitUser select 34,2,1insert @SubscriptionOrgs select 20,2drop table #tmpselect top 100 rowid=identity(int,1,1),'10000' as d into #tmp from sysobjects select a.userid,b.SubscriptionId ,c.dd from @UnitUser a,@SubscriptionOrgs bcross join ( select top 6 (d+ convert(varchar(2),rowid)) as dd from #tmp ) c/*userid SubscriptionId dd ----------- -------------- ------- 34 20 10000134 20 10000234 20 10000334 20 10000434 20 10000534 20 100006*/ 我要插入的就是PermissionId为1和2的两种情况A: OrgUnitUser(UserID, OrgUnitID, OrgUnitTypeRoleId);B: SubscriptionOrgs(SubscriptionId, OrgUnitId)若A表中有记录:35, 20, 2B表中有记录: 21,20则C表C: UserPermission(SubscriptionID, UserID, PermissionId);中对应应该有记录: 21,35,100003 21,35,100004只是从A表中选择OrgUnitTypeRoleId为1或2的记录 happyflystone(仙林幽谷客) :您写得太复杂啦,我不懂~ declare @UnitUser table(UserID int,OrgUnitID int,OrgUnitTypeRoleId int)declare @SubscriptionOrgs table(SubscriptionId int,OrgUnitId int)--以上是定义二表insert @UnitUser select 34,2,1insert @SubscriptionOrgs select 20,2--以上插入数据drop table #tmp-- 删除表select top 100 rowid=identity(int,1,1),'10000' as d into #tmp from sysobjects --产生中间数据select a.userid,b.SubscriptionId ,c.dd from @UnitUser a,@SubscriptionOrgs bcross join ( select top 6 (d+ convert(varchar(2),rowid)) as dd from #tmp ) c-- SQL语句/*userid SubscriptionId dd ----------- -------------- ------- 34 20 10000134 20 10000234 20 10000334 20 10000434 20 10000534 20 100006*/ declare @UnitUser table(UserID int,OrgUnitID int,OrgUnitTypeRoleId int)declare @SubscriptionOrgs table(SubscriptionId int,OrgUnitId int)insert @UnitUser select 34,2,1insert @UnitUser select 34,3,2insert @SubscriptionOrgs select 20,2insert @SubscriptionOrgs select 30,3drop table #tmpselect top 100 rowid=identity(int,1,1),'10000' as d into #tmp from sysobjects select a.userid,b.SubscriptionId ,c.dd from @UnitUser a,@SubscriptionOrgs bcross join ( select top 6 (d+ convert(varchar(2),rowid)) as dd from #tmp ) c where a.OrgUnitTypeRoleId = 1 and a.OrgUnitID = b.OrgUnitIDunion allselect a.userid,b.SubscriptionId ,c.dd from @UnitUser a,@SubscriptionOrgs bcross join ( select top 2 (d+ convert(varchar(2),rowid)) as dd from #tmp where rowid >2 ) c where a.OrgUnitTypeRoleId = 2 and a.OrgUnitID = b.OrgUnitID/*userid SubscriptionId dd ----------- -------------- ------- 34 20 10000134 20 10000234 20 10000334 20 10000434 20 10000534 20 10000634 30 10000334 30 100004*/ 下面这个sql语句怎么写? 一条看不懂的select语句 不好意思没分,触发器问题,奇怪!!! 同字段多个like 查询交集 同步时表生成的rowguid有什么作用 MDImain窗体如何禁止最大化,有没有属性直接设置?急~~ 请问某一天是星期几,如何判断? 有内部函数吗? 请问如何用脚本改变服务器登录帐户的密码? 怎么样将一个表中的相应字段的数据写到另一个表中去??高分奉送! 如何找出相同的记录? 触发器问题 请教一个平均值的sql语句
1的时候是这样
20,34,100001
20,34,100002
20,34,100003
20,34,100004
20,34,100005
20,34,100006
是2的话,PermissionId将是“100003”,“100004”也就是说增加两条记录
那是什么结果???????
写出来
declare @UnitUser table(UserID int,OrgUnitID int,OrgUnitTypeRoleId int)
declare @SubscriptionOrgs table(SubscriptionId int,OrgUnitId int)insert @UnitUser select 34,2,1
insert @SubscriptionOrgs select 20,2
drop table #tmp
select top 100 rowid=identity(int,1,1),'10000' as d into #tmp from sysobjects select a.userid,b.SubscriptionId ,c.dd from @UnitUser a,@SubscriptionOrgs b
cross join
( select top 6 (d+ convert(varchar(2),rowid)) as dd from #tmp ) c
/*
userid SubscriptionId dd
----------- -------------- -------
34 20 100001
34 20 100002
34 20 100003
34 20 100004
34 20 100005
34 20 100006
*/
B: SubscriptionOrgs(SubscriptionId, OrgUnitId)
若A表中有记录:35, 20, 2
B表中有记录: 21,20
则C表
C: UserPermission(SubscriptionID, UserID, PermissionId);
中对应应该有记录:
21,35,100003
21,35,100004
只是从A表中选择OrgUnitTypeRoleId为1或2的记录
declare @SubscriptionOrgs table(SubscriptionId int,OrgUnitId int)
--以上是定义二表
insert @UnitUser select 34,2,1
insert @SubscriptionOrgs select 20,2
--以上插入数据
drop table #tmp
-- 删除表
select top 100 rowid=identity(int,1,1),'10000' as d into #tmp from sysobjects
--产生中间数据
select a.userid,b.SubscriptionId ,c.dd from @UnitUser a,@SubscriptionOrgs b
cross join
( select top 6 (d+ convert(varchar(2),rowid)) as dd from #tmp ) c
-- SQL语句
/*
userid SubscriptionId dd
----------- -------------- -------
34 20 100001
34 20 100002
34 20 100003
34 20 100004
34 20 100005
34 20 100006
*/
declare @SubscriptionOrgs table(SubscriptionId int,OrgUnitId int)insert @UnitUser select 34,2,1
insert @UnitUser select 34,3,2
insert @SubscriptionOrgs select 20,2
insert @SubscriptionOrgs select 30,3drop table #tmp
select top 100 rowid=identity(int,1,1),'10000' as d into #tmp from sysobjects select a.userid,b.SubscriptionId ,c.dd from @UnitUser a,@SubscriptionOrgs b
cross join
( select top 6 (d+ convert(varchar(2),rowid)) as dd from #tmp ) c
where a.OrgUnitTypeRoleId = 1 and a.OrgUnitID = b.OrgUnitIDunion all
select a.userid,b.SubscriptionId ,c.dd from @UnitUser a,@SubscriptionOrgs b
cross join
( select top 2 (d+ convert(varchar(2),rowid)) as dd from #tmp where rowid >2 ) c
where a.OrgUnitTypeRoleId = 2 and a.OrgUnitID = b.OrgUnitID/*
userid SubscriptionId dd
----------- -------------- -------
34 20 100001
34 20 100002
34 20 100003
34 20 100004
34 20 100005
34 20 100006
34 30 100003
34 30 100004
*/