设定了
Select ID as RID,
((Case when No1=08 then 1 else 0 end)+
(Case when No2=11 then 1 else 0 end)+
(Case when No3=12 then 1 else 0 end)+
(Case when No4=14 then 1 else 0 end)+
(Case when No5=18 then 1 else 0 end)+
(Case when No6=22 then 1 else 0 end)) as RpNumber Into #RepTable
From [SSBallDB].[dbo].[历史号码] Select ID ,号码
FROM [SSBallDB].[dbo].[历史号码]
Where 1=1
and ID in(Select ID From #RepTable left join [SSBallDB].[dbo].[历史号码] on #RepTable.RID=历史号码.ID Where 1=1
and (RpNumber between 0 and 0))
Drop Table #RepTable 查询有882行数据符合条件,但如何把“882”写到新建字段“R0”里,请用SQL语句完成。
Select ID as RID,
((Case when No1=08 then 1 else 0 end)+
(Case when No2=11 then 1 else 0 end)+
(Case when No3=12 then 1 else 0 end)+
(Case when No4=14 then 1 else 0 end)+
(Case when No5=18 then 1 else 0 end)+
(Case when No6=22 then 1 else 0 end)) as RpNumber Into #RepTable
From [SSBallDB].[dbo].[历史号码] Select ID ,号码
FROM [SSBallDB].[dbo].[历史号码]
Where 1=1
and ID in(Select ID From #RepTable left join [SSBallDB].[dbo].[历史号码] on #RepTable.RID=历史号码.ID Where 1=1
and (RpNumber between 0 and 0))
Drop Table #RepTable 查询有882行数据符合条件,但如何把“882”写到新建字段“R0”里,请用SQL语句完成。
解决方案 »
- 这段sql代码是什么意思?
- 怎样自定义设定主键的自增方式?
- 求一条sql语句怎么写, 一个表单中的一个字段, 是另一个表单中的id, 要求查出另一个表单的某个字段
- 如何在当前服务器上附加只有主数据文件(即只有*.MDF文件)的数据库!?
- Sql 2005 查询问题 In 查询 麻烦大家看一看
- 由于我的几个帖子没有给分给大家,深表歉意,现在转贴一篇文章给大家,希望能对初学者有点帮助。
- 重发求助:sql2008中的存储过程、触发器解密问题。
- 先请而后教,查询表中最后条记录,sql怎么写?
- 一个有关case语句问题
- 为什么我在设置发布服务器的时候说我的不是有效的路径或文件名?
- 能用SQL语句完成吗?我都有点晕了
- 大家帮忙 写个 一个数据库更新语句
--try
declare @count int
set @count=@@rowcount
if @count >0
begin
insert into 历史号码(ro)
values(@count)
end
IF OBJECT_ID('表一')>0
DROP TABLE 表一
GO
;WITH t AS
(
SELECT TOP 33 rn=ROW_NUMBER()OVER(ORDER BY @@spid)
FROM sysobjects
)
SELECT TOP 100 a.rn N01,b.rn N02,c.rn N03,d.rn N04,e.rn N05,f.rn N06,
R0=0,R1=0,R2=0,R3=0,R4=0,R5=0,R6=0
INTO 表一
FROM t a
JOIN t b ON a.rn<b.rn
JOIN t c ON b.rn<c.rn
JOIN t d ON c.rn<d.rn
JOIN t e ON d.rn<e.rn
JOIN t f ON e.rn<f.rn
ORDER BY 1,2,3,4,5,6
--创建表二:
IF OBJECT_ID('[表二]') IS NOT NULL
DROP TABLE [表二]
GO
CREATE TABLE [表二] ([N01] [nvarchar](10),[N02] [nvarchar](10),[N03] [int],[N04] [int],[N05] [int],[N06] [int])
INSERT INTO [表二]
SELECT '03','07','13','23','27','30' UNION ALL
SELECT '07','13','17','26','32','33' UNION ALL
SELECT '10','11','13','16','19','30' UNION ALL
SELECT '10','19','20','21','23','32' UNION ALL
SELECT '02','05','11','26','30','32' UNION ALL
SELECT '01','02','14','23','28','29' UNION ALL
SELECT '08','12','20','22','30','33' UNION ALL
SELECT '02','15','19','24','31','32'--SELECT * FROM [表二]---->SQL查询如下:--第三:创建一个表值函数
IF OBJECT_ID('f_table')>0
DROP FUNCTION f_table
GO
CREATE FUNCTION f_table(@1 INT,@2 INT,@3 INT,@4 INT,@5 INT,@6 INT,@type INT)
RETURNS @r TABLE(n VARCHAR(1000))
AS
BEGIN
IF @type = 1
INSERT @r
SELECT n='%,'+LTRIM(@1)+',%' UNION ALL
SELECT '%,'+LTRIM(@2)+',%' UNION ALL
SELECT '%,'+LTRIM(@3)+',%' UNION ALL
SELECT '%,'+LTRIM(@4)+',%' UNION ALL
SELECT '%,'+LTRIM(@5)+',%' UNION ALL
SELECT '%,'+LTRIM(@6)+',%'
ELSE IF @type=2
WITH t AS
(
SELECT n=@1 UNION ALL
SELECT @2 UNION ALL
SELECT @3 UNION ALL
SELECT @4 UNION ALL
SELECT @5 UNION ALL
SELECT @6
)
INSERT @r
SELECT '%,'+LTRIM(a.n)+',%,'+LTRIM(b.n)+',%'
FROM t a,t b
WHERE a.n<b.n
ELSE IF @type=3
WITH t AS
(
SELECT n=@1 UNION ALL
SELECT @2 UNION ALL
SELECT @3 UNION ALL
SELECT @4 UNION ALL
SELECT @5 UNION ALL
SELECT @6
)
INSERT @r
SELECT '%,'+LTRIM(a.n)+',%,'+LTRIM(b.n)+',%,'+LTRIM(c.n)+',%'
FROM t a,t b,t c
WHERE a.n<b.n AND b.n<c.n
ELSE IF @type=4
WITH t AS
(
SELECT n=@1 UNION ALL
SELECT @2 UNION ALL
SELECT @3 UNION ALL
SELECT @4 UNION ALL
SELECT @5 UNION ALL
SELECT @6
)
INSERT @r
SELECT '%,'+LTRIM(a.n)+',%,'+LTRIM(b.n)+',%,'+LTRIM(c.n)+',%,'+LTRIM(d.n)+',%'
FROM t a,t b,t c,t d
WHERE a.n<b.n AND b.n<c.n AND c.n<d.n
ELSE IF @type=5
WITH t AS
(
SELECT n=@1 UNION ALL
SELECT @2 UNION ALL
SELECT @3 UNION ALL
SELECT @4 UNION ALL
SELECT @5 UNION ALL
SELECT @6
)
INSERT @r
SELECT '%,'+LTRIM(a.n)+',%,'+LTRIM(b.n)+',%,'+LTRIM(c.n)+',%,'+LTRIM(d.n)+',%,'+LTRIM(e.n)+',%'
FROM t a,t b,t c,t d,t e
WHERE a.n<b.n AND b.n<c.n AND c.n<d.n AND d.n<e.n
ELSE IF @type=6
INSERT @r
SELECT '%,'+LTRIM(@1)+',%,'+LTRIM(@2)+',%,'+LTRIM(@3)+',%,'+LTRIM(@4)+',%,'+LTRIM(@5)+',%,'+LTRIM(@6)+',%'
RETURN
END
GO--SELECT * FROM f_table(1,2,3,4,5,6,1)
--第四:游标更新表一数据:
SET NOCOUNT ON
DECLARE @1 INT,@2 INT,@3 INT,@4 INT,@5 INT,@6 INT
DECLARE c CURSOR FOR
SELECT N01,N02,N03,N04,N05,N06
FROM 表一
FOR UPDATE OF R0,R1,R2,R3,R4,R5,R6
OPEN c
FETCH c INTO @1,@2,@3,@4,@5,@6
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE 表一
SET R1 = (
SELECT COUNT(1)
FROM 表二
WHERE EXISTS(
SELECT 1
FROM dbo.f_table(@1, @2, @3, @4, @5, @6,1)
WHERE PATINDEX(
n, ','+LTRIM(N01)+',,'+LTRIM(N02)+',,'+LTRIM(N03)+',,'+
LTRIM(N04)+',,'+LTRIM(N05)+',,'+LTRIM(N06)+','
)>0
)
)
,R2 = (
SELECT COUNT(1)
FROM 表二
WHERE EXISTS(
SELECT 1
FROM dbo.f_table(@1, @2, @3, @4, @5, @6,2)
WHERE PATINDEX(
n, ','+LTRIM(N01)+',,'+LTRIM(N02)+',,'+LTRIM(N03)+',,'+
LTRIM(N04)+',,'+LTRIM(N05)+',,'+LTRIM(N06)+','
)>0
)
)
,R3 = (
SELECT COUNT(1)
FROM 表二
WHERE EXISTS(
SELECT 1
FROM dbo.f_table(@1, @2, @3, @4, @5, @6,3)
WHERE PATINDEX(
n, ','+LTRIM(N01)+',,'+LTRIM(N02)+',,'+LTRIM(N03)+',,'+
LTRIM(N04)+',,'+LTRIM(N05)+',,'+LTRIM(N06)+','
)>0
)
)
,R4 = (
SELECT COUNT(1)
FROM 表二
WHERE EXISTS(
SELECT 1
FROM dbo.f_table(@1, @2, @3, @4, @5, @6,4)
WHERE PATINDEX(
n, ','+LTRIM(N01)+',,'+LTRIM(N02)+',,'+LTRIM(N03)+',,'+
LTRIM(N04)+',,'+LTRIM(N05)+',,'+LTRIM(N06)+','
)>0
)
)
,R5 = (
SELECT COUNT(1)
FROM 表二
WHERE EXISTS(
SELECT 1
FROM dbo.f_table(@1, @2, @3, @4, @5, @6,5)
WHERE PATINDEX(
n, ','+LTRIM(N01)+',,'+LTRIM(N02)+',,'+LTRIM(N03)+',,'+
LTRIM(N04)+',,'+LTRIM(N05)+',,'+LTRIM(N06)+','
)>0
)
)
,R6 = (
SELECT COUNT(1)
FROM 表二
WHERE EXISTS(
SELECT 1
FROM dbo.f_table(@1, @2, @3, @4, @5, @6,6)
WHERE PATINDEX(
n, ','+LTRIM(N01)+',,'+LTRIM(N02)+',,'+LTRIM(N03)+',,'+
LTRIM(N04)+',,'+LTRIM(N05)+',,'+LTRIM(N06)+','
)>0
)
)
,R0 = (
SELECT COUNT(1)
FROM 表二
WHERE NOT EXISTS(
SELECT 1
FROM dbo.f_table(@1, @2, @3, @4, @5, @6,1)
WHERE PATINDEX(
n, ','+LTRIM(N01)+',,'+LTRIM(N02)+',,'+LTRIM(N03)+',,'+
LTRIM(N04)+',,'+LTRIM(N05)+',,'+LTRIM(N06)+','
)>0
)
)
WHERE CURRENT OF c
FETCH c INTO @1,@2,@3,@4,@5,@6
END
CLOSE c
DEALLOCATE c
SET NOCOUNT OFF
SELECT * FROM 表一