22,207
社区成员
发帖
与我相关
我的任务
分享有点难度的SQL，求教
有三个表
clocks(设备表)
clock_id //设备编号
subdevice(子设备表)
clock_id //设备编号
sub_id //子设备编号（只有1,2,3,4四个值）
userauth(用户设备权限表)
emp_no //工号
clock_id //设备编号
subctrl //子设备编号组合（1,2,3,4的组合值）
数据分别如下
clock_id
1
clock_id sub_id
1 1
1 2
emp_no clock_id subctrl
000001 1 3
如何通过查询实现如下效果
emp_no clock_id sub_id
000001 1 1
000001 1 2
clocks(设备表)
clock_id //设备编号
subdevice(子设备表)
clock_id //设备编号
sub_id //子设备编号（只有1,2,3,4四个值）
userauth(用户设备权限表)
emp_no //工号
clock_id //设备编号
subctrl //子设备编号组合（1,2,3,4的组合值）
数据分别如下
clock_id
1
clock_id sub_id
1 1
1 2
emp_no clock_id subctrl
000001 1 3
如何通过查询实现如下效果
emp_no clock_id sub_id
000001 1 1
000001 1 2
...全文
请发表友善的回复…
发表回复
道素 2017-01-06
- 打赏
- 举报
你这里比较麻烦的其实就死拆解数字方式吧，比的例子其实算是特例，我觉得你这么定义数据库其实不严谨，有些数据的组合方式不仅仅是一种，比3，可以与3， 也可以是1和2
下面的语句不一定很理想，但是列出了4个数组的自合方式，可以看出3，4，5，6，7都有两种方案
（你的例子数据是两个个数组合）
;WITH t(id) AS(
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
)
, tt AS(
SELECT sub.ctrl,c.id
,DENSE_RANK()OVER(PARTITION BY sub.ctrl ORDER BY NULLIF(t2.id,t1.id),NULLIF(t3.id,t2.id),NULLIF(t4.id,t3.id)) AS rn
,ROW_NUMBER()OVER(PARTITION BY t1.id,NULLIF(t2.id,t1.id),NULLIF(t3.id,t2.id),NULLIF(t4.id,t3.id) ORDER BY c.id) ind
FROM t AS t1
LEFT JOIN t AS t2 ON t2.id>=t1.id
LEFT JOIN t AS t3 ON t3.id>=t2.id
LEFT JOIN t AS t4 ON t4.id>=t3.id
CROSS APPLY(VALUES(t1.id+ISNULL(NULLIF(t2.id,t1.id),0)+ISNULL(NULLIF(t3.id,t2.id),0)+ISNULL(NULLIF(t4.id,t3.id),0))) sub(ctrl)
CROSS APPLY(VALUES(t1.id),(NULLIF(t2.id,t1.id)),(NULLIF(t3.id,t2.id)),(NULLIF(t4.id,t3.id))) c(id)
WHERE c.id IS NOT NULL
)
SELECT DISTINCT ctrl,[1],[2],[3],[4] FROM tt AS t1
PIVOT(MAX(t1.id) FOR ind IN ([1],[2],[3],[4])) p
ORDER BY ctrl
ctrl 1 2 3 4
----------- ----------- ----------- ----------- -----------
1 1 NULL NULL NULL
2 2 NULL NULL NULL
3 1 2 NULL NULL
3 3 NULL NULL NULL
4 1 3 NULL NULL
4 4 NULL NULL NULL
5 1 4 NULL NULL
5 2 3 NULL NULL
6 1 2 3 NULL
6 2 4 NULL NULL
7 1 2 4 NULL
7 3 4 NULL NULL
8 1 3 4 NULL
9 2 3 4 NULL
10 1 2 3 4
Ginnnnnnnn 2017-01-06
- 打赏
- 举报
根据楼主的意思，是不是你的 subctrl 这个字段其实是不是这样组合
1 1
2 2
3 4
4 8
这样控制处理的
--> 测试数据: [clocks]
if object_id('[clocks]') is not null drop table [clocks]
create table [clocks] (clock_id int)
insert into [clocks]
select 1
--> 测试数据: [subdevice]
if object_id('[subdevice]') is not null drop table [subdevice]
create table [subdevice] (clock_id int,sub_id int)
insert into [subdevice]
select 1,1 union all
select 1,2
--> 测试数据: [userauth]
if object_id('[userauth]') is not null drop table [userauth]
create table [userauth] (emp_no varchar(6),clock_id int,subctrl int)
insert into [userauth]
select '000001',1,3
SELECT a.emp_no,b.clock_id,b.sub_id
FROM dbo.userauth a
INNER JOIN dbo.subdevice b ON a.clock_id = b.clock_id AND a.subctrl & b.sub_id > 0
DROP TABLE [clocks]
DROP TABLE [subdevice]
DROP TABLE [userauth]
CCC的 2017-01-06
- 打赏
- 举报
[quote=引用 10 楼 Tiger_Zhao 的回复:]
[Quote=引用 4 楼 hnlg81004 的回复:]subctrl可能是1-15中的任何一个[/Quote]
subctrl 是位标志，而 sub_id 是位的序号？
/* 测试数据
CREATE TABLE clocks(clock_id int)
INSERT INTO clocks
SELECT 1
CREATE TABLE subdevice(clock_id int, sub_id int)
INSERT INTO subdevice
SELECT 1,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 1,4
CREATE TABLE userauth(emp_no varchar(6), clock_id int, subctrl int)
INSERT INTO userauth
SELECT '000001',1,3 UNION ALL
SELECT '000002',1,2 UNION ALL
SELECT '000003',1,9
GO
*/
SELECT u.emp_no,
u.clock_id,
s.sub_id
FROM userauth u
JOIN subdevice s
ON u.clock_id = s.clock_id
WHERE (POWER(2,s.sub_id-1) & u.subctrl) <> 0emp_no clock_id sub_id
------ ----------- -----------
000001 1 1
000001 1 2
000002 1 2
000003 1 1
000003 1 4
Mr_Nice 2017-01-06
- 打赏
- 举报
--> 测试数据: [clocks]
IF OBJECT_ID('[clocks]') IS NOT NULL
DROP TABLE [clocks];
CREATE TABLE [clocks] ( clock_id INT );
INSERT INTO [clocks]
SELECT 1;
--> 测试数据: [subdevice]
IF OBJECT_ID('[subdevice]') IS NOT NULL
DROP TABLE [subdevice];
CREATE TABLE [subdevice]
(
clock_id INT ,
sub_id INT
);
INSERT INTO [subdevice]
SELECT 1 ,
1
UNION ALL
SELECT 1 ,
2
UNION ALL
SELECT 1 ,
3
UNION ALL
SELECT 1 ,
4;
--> 测试数据: [userauth]
IF OBJECT_ID('[userauth]') IS NOT NULL
DROP TABLE [userauth];
CREATE TABLE [userauth]
(
emp_no VARCHAR(6) ,
clock_id INT ,
subctrl INT
);
INSERT INTO [userauth]
SELECT '000001' ,
1 ,
10;
SELECT *
FROM [clocks];
SELECT *
FROM [subdevice];
SELECT *
FROM [userauth];
WITH TT
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY GETDATE() ) AS no ,
*
FROM subdevice
),
T1
AS ( SELECT TT.clock_id ,
TT.sub_id ,
( SELECT SUM(sub_id)
FROM TT A
WHERE A.no <= TT.no
) AS sno
FROM TT
)
SELECT u.emp_no ,
c.clock_id ,
T1.sub_id ,
u.subctrl
FROM dbo.userauth u
INNER JOIN dbo.clocks c ON u.clock_id = c.clock_id
INNER JOIN T1 ON T1.clock_id = c.clock_id
WHERE T1.sno <= u.subctrl;
/*
emp_no clock_id sub_id subctrl
000001 1 1 10
000001 1 2 10
000001 1 3 10
000001 1 4 10
*/
Tiger_Zhao 2017-01-06
- 打赏
- 举报
[Quote=引用 4 楼 hnlg81004 的回复:]subctrl可能是1-15中的任何一个[/Quote]
subctrl 是位标志，而 sub_id 是位的序号？
subctrl 是位标志，而 sub_id 是位的序号？
/* 测试数据
CREATE TABLE clocks(clock_id int)
INSERT INTO clocks
SELECT 1
CREATE TABLE subdevice(clock_id int, sub_id int)
INSERT INTO subdevice
SELECT 1,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 1,4
CREATE TABLE userauth(emp_no varchar(6), clock_id int, subctrl int)
INSERT INTO userauth
SELECT '000001',1,3 UNION ALL
SELECT '000002',1,2 UNION ALL
SELECT '000003',1,9
GO
*/
SELECT u.emp_no,
u.clock_id,
s.sub_id
FROM userauth u
JOIN subdevice s
ON u.clock_id = s.clock_id
WHERE (POWER(2,s.sub_id-1) & u.subctrl) <> 0emp_no clock_id sub_id
------ ----------- -----------
000001 1 1
000001 1 2
000002 1 2
000003 1 1
000003 1 4CCC的 2017-01-06
- 打赏
- 举报
如果subdevice是这种 那么userauth可能是
clock_id sub_id emp_no clock_id subctrl emp_no clock_id subctrl emp_no clock_id subctrl
1 1 000001 1 1 000001 1 2 000001 1 3
1 2
emp_no clock_id subctrl
000001 1 3
这种情况你们查出的
emp_no clock_id sub_id
000001 1 1
000001 1 2
---------------------------------------------
如果是
emp_no clock_id subctrl
000001 1 2
结果应该是
emp_no clock_id sub_id
000001 1 2
---------------------------------------------
如果是
emp_no clock_id subctrl
000001 1 1
结果应该是
emp_no clock_id sub_id
000001 1 1
CCC的 2017-01-06
- 打赏
- 举报
不好意思，是我没说明清楚，
如果subdevice是这种 那么userauth可能是
clock_id sub_id emp_no clock_id subctrl emp_no clock_id subctrl emp_no clock_id subctrl
1 1 000001 1 1 000001 1 2 000001 1 3
1 2
如果subdevice是这种 那么userauth可能是
clock_id sub_id emp_no clock_id subctrl emp_no clock_id subctrl ... emp_no clock_id subctrl
1 1 000001 1 1 000001 1 2 000001 1 15
1 2
1 3
1 4
Mr_Nice 2017-01-06
- 打赏
- 举报
没看懂,你要的最后结果写一下.
CCC的 2017-01-06
- 打赏
- 举报
不好意思，是我没说明清楚，
如果subdevice是这种 那么userauth可能是
clock_id sub_id emp_no clock_id subctrl emp_no clock_id subctrl emp_no clock_id subctrl
1 1 000001 1 1 000001 1 2 000001 1 3
1 2
如果subdevice是这种 那么userauth可能是
clock_id sub_id emp_no clock_id subctrl emp_no clock_id subctrl emp_no clock_id subctrl ... emp_no clock_id subctrl
1 1 000001 1 1 000001 1 2 000001 1 3 000001 1 15
1 2
1 3
1 4
学海无涯-回头是岸 2017-01-06
- 打赏
- 举报
你要的结果 没有体现那个啊
CCC的 2017-01-06
- 打赏
- 举报
emp_no clock_id subctrl
000001 1 2
这样结果就不对了，subctrl可能是1-15中的任何一个
学海无涯-回头是岸 2017-01-06
- 打赏
- 举报
直接3张表 做join 连接就可以了
select C.emp_no,A.clock_id ,B.clock_id FROM C
JOIN A ON C.clock_id =A.clock_id
JOIN B ON C.clock_id =B.clock_id
CCC的 2017-01-06
- 打赏
- 举报
谢谢，试了你这个不行，我没说清楚，这个例子举得有点问题，上面的例子只是其中的一种
如果是
emp_no clock_id subctrl
000001 1 2
这样结果就不对了，subctrl可能是1-15中的任何一个
subdevice(子设备表)数据有三种情况
clock_id sub_id
1 1
clock_id sub_id
1 1
1 2
clock_id sub_id
1 1
1 2
1 3
1 4
Mr_Nice 2017-01-06
- 打赏
- 举报
--> 测试数据: [clocks]
if object_id('[clocks]') is not null drop table [clocks]
create table [clocks] (clock_id int)
insert into [clocks]
select 1
--> 测试数据: [subdevice]
if object_id('[subdevice]') is not null drop table [subdevice]
create table [subdevice] (clock_id int,sub_id int)
insert into [subdevice]
select 1,1 union all
select 1,2
--> 测试数据: [userauth]
if object_id('[userauth]') is not null drop table [userauth]
create table [userauth] (emp_no varchar(6),clock_id int,subctrl int)
insert into [userauth]
select '000001',1,3
select * from [clocks]
select * from [subdevice]
select * from [userauth]
SELECT u.emp_no,c.clock_id,s.sub_id
FROM dbo.userauth u
LEFT JOIN dbo.clocks c ON u.clock_id=c.clock_id
LEFT JOIN dbo.subdevice s ON s.clock_id = c.clock_id
/*
emp_no clock_id sub_id
000001 1 1
000001 1 2*/
