我的数据库是这样的:
Id int identity(1,1) primary key not null,
Category varchar(10) not null, --收入、支出
Item varchar(20) not null, --项目:音响、键盘
Date datetime not null, --日期
MyMoney money not null, --金额
Re varchar(max) --备注我想输出的数据是这样的:
日期 收入 支出
2008-11-11 5.00 2.00
2008-12-12 10.00 7.00请问各位大虾这样该怎么做啊?第一个回答对的给80分!希望大家尽快回复。
Id int identity(1,1) primary key not null,
Category varchar(10) not null, --收入、支出
Item varchar(20) not null, --项目:音响、键盘
Date datetime not null, --日期
MyMoney money not null, --金额
Re varchar(max) --备注我想输出的数据是这样的:
日期 收入 支出
2008-11-11 5.00 2.00
2008-12-12 10.00 7.00请问各位大虾这样该怎么做啊?第一个回答对的给80分!希望大家尽快回复。
from (select sum(MyMoney),Date.year+Date.Month+Date.day as sdate from table1 where categ='支出' group by sData ) a
, (select sum(MyMoney),Date.year+Date.Month+Date.day as sdate from table1 where categ='收入' group by sData ) b
where a.sdate=b.sDate
SUM(CASE WHEN Category='收入' THEN MyMoney END),
SUM(CASE WHEN Category='支出' THEN MyMoney END)
FROM table_name
GROUP BY CONVERT(varchar(100),Date, 23)
SUM(CASE WHEN Category='收入' THEN MyMoney END) AS 收入,
SUM(CASE WHEN Category='支出' THEN MyMoney END) AS 支出
FROM table_name
GROUP BY CONVERT(varchar(100),Date, 23)
1楼具体代码怎么写,希望有代码?
4楼的代码报错,不知道为什么?
下面的函数可以按字符分割字符串SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOCREATE FUNCTION [dbo].[Uinatlex_Split]
(
@str VARCHAR(1024),
@split VARCHAR(10),
@index INT
)
RETURNS VARCHAR(1024)
AS
BEGIN
DECLARE @location INT
DECLARE @start INT
DECLARE @next INT
DECLARE @seed INT SET @str=LTRIM(RTRIM(@str))
SET @start=1
SET @next=1
SET @seed=LEN(@split)
SET @location=CHARINDEX(@split,@str)
WHILE @location<>0 and @index>@next
BEGIN
SET @start=@location+@seed
SET @location=CHARINDEX(@split,@str,@start)
SET @next=@next+1
END
IF @location =0 SELECT @location =LEN(@str)+1
RETURN SUBSTRING(@str,@start,@location-@start)
END比如你是按 '|'分割的,而收入再前
那么sql就是SELECT Date AS 日期,dbo.Uinatlex_Split(Category,'|',0) AS 收入,dbo.Uinatlex_Split(Category,'|',1) AS 支出 FROM TABLE
SUM(CASE WHEN Category='收入' THEN MyMoney END),
SUM(CASE WHEN Category='支出' THEN MyMoney END)
FROM table_name
GROUP BY CONVERT(varchar(100),Date, 23)
declare @sql varchar(max),@str varchar(max)
set @str=stuff(replace(replace((select distinct Date from table for xml auto)
,'<table Date="',','),'"/>',''),1,1,'')
set @sql ='select Date,'+ @str+' from table pivot (max(Category) for Date in ('+@str +'))as pvt'
exec(@sql)未測試