1、SELECT T2.kid,T2.datetime,T1.name FROM 表二 T2 INNER JOIN 表一 T1 ON T2.id=T1.id
--1. Select A.kid, A.[datetime], B.name Into 表三 From 表二 A Inner Join 表一 B On A.id=B.id
--2Select Max(Case When Len(id) = 1 Then name Else '' End) As name1, Max(Case When Len(id) <>1 Then name Else '' End) As name2 From TableName可以實現,但不知道要求是否完全符合
2、如果是在SQL Server2000里面,需要调用函数解决,把除了编号以外的需要放入name2的内容使用函数组合起来,示例函数: create Function F_GetReasonName(@TyreBarcode CHAR(12),@OrderId VARCHAR(20)) RETURNS VARCHAR(8000)AS
BEGIN DECLARE @s1 VARCHAR(8000) SELECT @s1='' SELECT @s1=@s1+'、'+T2.ReasonName+'' FROM tb_QU_TyreCheckReasonInfo T1 LEFT JOIN tb_QU_BadnessProductBadReason T2 ON T1.ReasonID=T2.ReasonID WHERE T1.TyreBarcode=@TyreBarcode and T1.OrderId=@OrderId SET @s1=stuff(@s1,1,1,'') RETURN @s1 END 然后再用SELECT语句把内容查询出来!
2) select case when id like '[0-9]' then name end as name1, case when id like '[0-9][0-9][0-9]' then name end as name2 from 表一3) select [date], sum(case when inout=1 then [money] else 0 end) as [in], sum(case when inout=0 then [money] else 0 end) as [out], (select sum(case when inout=1 then [money] else -[money] end) from 表一 where [date]<=a.[date]) as [money] from 表一 a group by [date]
--3. Select [date], SUM(Case inout When 1 Then [money] Else 0 End) As [in], SUM(Case inout When 0 Then [money] Else 0 End) As [out], (Select SUM(Case inout When 1 Then [money] Else -[money] End) From 表一 Where [date]<=A.[date]) As [money] From 表一 A Group By [date]
1 SELECT kid,datetime,name into 表3 FROM 表1 a INNER JOIN 表2 b ON a.id=b.id2 INSERT 表2(Name1,Name2) SELECT CASE WHEN id LIKE '[0-9]0[0-9]' THEN '' ELSE name END, CASE WHEN id LIKE '[0-9]0[0-9]' THEN Name ELSE '' END FROM 表1 3 SELECT [date], [IN]=SUM(CASE inout WHEN 1 THEN [money] ELSE 0 END), [out]=SUM(CASE inout WHEN 0 THEN [money] ELSE 0 END), [money]=SUM([money]) FROM 表1 GROUP BY [date]未测试,可能有手误。 总之这样的方法是可行的。
Select
A.kid,
A.[datetime],
B.name
Into 表三
From 表二 A
Inner Join 表一 B
On A.id=B.id
Max(Case When Len(id) = 1 Then name Else '' End) As name1,
Max(Case When Len(id) <>1 Then name Else '' End) As name2
From TableName可以實現,但不知道要求是否完全符合
create Function F_GetReasonName(@TyreBarcode CHAR(12),@OrderId VARCHAR(20))
RETURNS VARCHAR(8000)AS
BEGIN
DECLARE @s1 VARCHAR(8000)
SELECT @s1=''
SELECT @s1=@s1+'、'+T2.ReasonName+''
FROM tb_QU_TyreCheckReasonInfo T1
LEFT JOIN tb_QU_BadnessProductBadReason T2
ON T1.ReasonID=T2.ReasonID
WHERE T1.TyreBarcode=@TyreBarcode and T1.OrderId=@OrderId
SET @s1=stuff(@s1,1,1,'')
RETURN @s1
END
然后再用SELECT语句把内容查询出来!
select case when id like '[0-9]' then name end as name1,
case when id like '[0-9][0-9][0-9]' then name end as name2
from 表一3)
select [date],
sum(case when inout=1 then [money] else 0 end) as [in],
sum(case when inout=0 then [money] else 0 end) as [out],
(select sum(case when inout=1 then [money] else -[money] end) from 表一 where [date]<=a.[date]) as [money]
from 表一 a
group by [date]
Select
[date],
SUM(Case inout When 1 Then [money] Else 0 End) As [in],
SUM(Case inout When 0 Then [money] Else 0 End) As [out],
(Select SUM(Case inout When 1 Then [money] Else -[money] End) From 表一 Where [date]<=A.[date]) As [money]
From 表一 A
Group By [date]
SELECT kid,datetime,name into 表3
FROM 表1 a
INNER JOIN 表2 b
ON a.id=b.id2
INSERT 表2(Name1,Name2)
SELECT CASE WHEN id LIKE '[0-9]0[0-9]' THEN '' ELSE name END,
CASE WHEN id LIKE '[0-9]0[0-9]' THEN Name ELSE '' END
FROM 表1
3
SELECT [date],
[IN]=SUM(CASE inout WHEN 1 THEN [money] ELSE 0 END),
[out]=SUM(CASE inout WHEN 0 THEN [money] ELSE 0 END),
[money]=SUM([money])
FROM 表1
GROUP BY [date]未测试,可能有手误。 总之这样的方法是可行的。