在SQL Server 2008中查询数据 [英] Query data in SQL server 2008

问题描述

大家好！

我有一个包含此表的数据库：

#Report：链接图像表

http://prntscr.com/8h43p4 [ ^ ]



如何查询输出数据以下结果：

http://prntscr.com/8h44r5 [ ^ ]



谢谢大家！

解决方案

试试这个

  DECLARE   @ TBL  表 
（
 ID  INT   IDENTITY ，
 REPORTDATE  DATETIME ，
 GAMENAME  VARCHAR （ 50 ），
 TO TAL  INT  
）
 
  INSERT   INTO   @ TBL （REPORTDATE，GAMENAME，TOTAL）
  SELECT  '  2015-09-05'，'  Ninja Runner'， 2000   UNION   ALL  
  SELECT  ' < span class =code-string> 2015-09-05'，'  ZigZag'， 1500   UNION   ALL  
  SELECT  '  2015-09-05'，'  Sky Force'， 8000   UNION   ALL  
  SELECT   '  2015-09-06'，'  ZigZag'， 300   UNION   ALL  
  SELECT  '  2015-09 -06'，'  Sky Force'， 2500   UNION   ALL  
  SELECT  '  2015-09-07'，' 忍者Runner'， 700   UNION   ALL  
  SELECT  '  2015-09- 07'，'  Sky Force'， 1000   UNION   ALL  
  SELECT  '  2015-09-08'， '  ZigZag'， 10000  
 
 
 < span class =code-keyword> SELECT  TMP.REPORTDATE，TMP.GAMENAME，ISNULL（T.TOTAL， 0 ）
  FROM  
（
  SELECT   distinct  GAMENAME，A.REPORTDATE 
  FROM   @ TBL  
  CROSS   JOIN  
（
  SELECT  '   2015-09-03'  as  '   REPORTDATE'  UNION   ALL  
  SELECT  '  2015-09-04'   UNION   ALL  
  SELECT  '  2015-09-05'  UNION   ALL  
  SELECT  '  2015-09-06'  UNION   ALL  
  SELECT  '  2015-09-07'  UNION   ALL  
  SELECT  '  2015-09-08' 
）A 
）TMP  LEFT   OUTER   JOIN   @ TBL  T  ON  TMP.GAMENAME = T.GAMENAME  AND  TMP.REPORTDATE = T.REPORTDATE 
 订单 按  1 ，  2  

从#report

Hi all!
I have a database with this table:
#Report: link image table
http://prntscr.com/8h43p4[^]

How to query output data the following results:
http://prntscr.com/8h44r5[^]

Thanks every one!

解决方案

Try this

DECLARE @TBL TABLE
(
	ID INT IDENTITY,
	REPORTDATE DATETIME,
	GAMENAME VARCHAR(50),
	TOTAL  INT
)

INSERT INTO @TBL (REPORTDATE, GAMENAME, TOTAL)
SELECT '2015-09-05', 'Ninja Runner', 2000 UNION ALL
SELECT '2015-09-05', 'ZigZag', 1500 UNION ALL
SELECT '2015-09-05', 'Sky Force', 8000 UNION ALL
SELECT '2015-09-06', 'ZigZag', 300 UNION ALL
SELECT '2015-09-06', 'Sky Force', 2500 UNION ALL
SELECT '2015-09-07', 'Ninja Runner', 700 UNION ALL
SELECT '2015-09-07', 'Sky Force', 1000 UNION ALL
SELECT '2015-09-08', 'ZigZag', 10000


SELECT TMP.REPORTDATE, TMP.GAMENAME, ISNULL(T.TOTAL, 0)
FROM
(
	SELECT distinct GAMENAME, A.REPORTDATE
	FROM	@TBL
	CROSS JOIN
	(
	SELECT '2015-09-03' as 'REPORTDATE' UNION ALL
	SELECT '2015-09-04' UNION ALL
	SELECT '2015-09-05' UNION ALL
	SELECT '2015-09-06' UNION ALL
	SELECT '2015-09-07' UNION ALL
	SELECT '2015-09-08'
	) A 
) TMP LEFT OUTER JOIN @TBL T ON TMP.GAMENAME = T.GAMENAME AND TMP.REPORTDATE = T.REPORTDATE
order by 1, 2

Select column_name, column_name, column_name from #report

这篇关于在SQL Server 2008中查询数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！