-- 데이터베이스의 사용자 테이블을 호출
--select * from sysobjects where xtype='U' order by name
-- 데이터베이스의 사용자 테이블 중 특정 필드가 있는 테이블만 호출한다.
select tbl.name, col.name colnm
from (
select id,
name
from sysobjects
where xtype = 'U'
--and name = 'tblBBS_DTQNA'
and name in ( select name from sysobjects where xtype='U' )
) tbl
inner join syscolumns col on col.id = tbl.id
left outer join sysindexkeys idx on idx.id = col.id and col.colid = idx.colid and indid = 1
where col.name in ( 'dtPubDate' )
order by tbl.name
SELECT
CONVERT(varchar(10),regDate,112) AS regDate,
COUNT(*) total,
COUNT(CASE maxOrderText WHEN 1 THEN maxOrderText ELSE null END) men,
COUNT(CASE maxOrderText WHEN 0 THEN maxOrderText ELSE null END) women,
sum(CASE WHEN maxOrder < 10 THEN 1 ELSE 0 END) age00,
sum(CASE WHEN maxOrder between 10 and 20 THEN 1 ELSE 0 END) age10,
sum(CASE WHEN maxOrder between 20 and 30 THEN 1 ELSE 0 END) age20,
sum(CASE WHEN maxOrder between 30 and 40 THEN 1 ELSE 0 END) age30,
sum(CASE WHEN maxOrder > 40 THEN 1 ELSE 0 END) age40
/*
(SELECT COUNT(*) FROM tblshopgoods WHERE maxOrder < 10 ) as age00,
(SELECT COUNT(*) FROM tblshopgoods WHERE maxOrder between 10 and 20) as age10,
(SELECT COUNT(*) FROM tblshopgoods WHERE maxOrder between 20 and 30) as age20,
(SELECT COUNT(*) FROM tblshopgoods WHERE maxOrder between 30 and 40) as age30,
(SELECT COUNT(*) FROM tblshopgoods WHERE maxOrder > 40) as age40
*/
FROM dbo.tblShopGoods
GROUP BY CONVERT(varchar(10),regDate,112)
declare @tstr varchar(100)
, @i int
, @tsql varchar(5000)
, @k int
create table #tbTemp
(aa varchar(50))
set @k = 1
while exists (select aa from tempTb where idx = @k)
Begin
select @tstr=aa from tempTb where idx = @k
select @i = charindex('||', @tstr)
if @i > 0
begin
while @i > 0
begin
insert into #tbTemp values(left(@tstr, charIndex('||', @tstr)-1))
select @tstr = substring(@tstr, charindex('||', @tstr) + 2, len(@tstr))
select @i = charindex('||' , @tstr)
end
select @tsql = left(@tsql, len(@tsql)-2)
insert into #tbTemp values (@tstr)
end
else
insert into #tbTemp values(@tstr)
set @k = @k + 1
End
select aa, count(aa) as cnt from #tbTemp
group by aa
order by aa
