请帮助Sql输出 [英] Please Help for Sql Output
本文介绍了请帮助Sql输出的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
假设一张桌子只包含
17-04
18-06
20-12
另一张桌子或只是我有
2012-2014
表示
conitas
2012
2013
2014
我希望输出像
17-04-2012
18-06-2012
20-12-2012
17-04 -2013
18-06-2013
20-12-2013
17-04-2014
18-06-2014
20-12-2014
如何得到这样的
解决方案
有很多方法可以做到这一点,具体取决于你真正想要的。没有常见的列,您需要决定是否要引入公共列或获取产品。
笛卡尔积将匹配第一个表中的每一行第二行中的每一行。
这可能不是你想要的,因为表1中的1000个条目和表2中的100个条目将导致100,000行有很多重复的信息。
或者,您可以使用联合输出数据,但不是并排(您需要制作)确保列类型在两个选项之间是兼容的,方法是使表列兼容或在选择中强制它们。
如果您可以更具体地解决您的问题我们可能会想出一个有效的解决方案。因此,指定两个表之间是否存在关系以及每个表中的条目数,然后我们可以创建一个SQL语句来检索所需的结果。
干杯
这样......
FIND_YRS(年)
(
选择( 选择 转换( int , left (' 2012-2014 ', 4 ))) as yr
union all
选择 yr + 1 来自 FIND_YRS wher e yr< (选择 转换( int , right (' 2012-2014 ' , 4 )))
)
select day_Month_Col + ' - ' + convert ( varchar ( 4 ),yr) as 日期
来自 DAY_MONTH_TABLE
交叉 加入 FIND_YRS
快乐编码!
:)
您好,
检查以下代码
- 表创建
CREATE 表 #Month(MonthVal VARCHAR ( 20 ))
INSERT INTO #Month(MonthVal) VALUES (' 17-04'),(' 18-06'),(' 20-12')
CREATE 表 #Year(YearRange SMALLINT )
INSERT INTO #Year(YearRange) VALUES ( 2012 ),( 2013 ),( 2014 )
- 实际查询
SELECT MonthVal + ' - + CAST(YearRange AS VARCHAR ( 5 ))
FROM #月,#年
问候,
GVPrabu
let say one table contain only
17-04
18-06
20-12
Another table or just i have
2012-2014
means
it conitas
2012
2013
2014
I want output like
17-04-2012
18-06-2012
20-12-2012
17-04-2013
18-06-2013
20-12-2013
17-04-2014
18-06-2014
20-12-2014
How to get like this
解决方案
Hi,
there are a number of ways to do this, depending on what you really want. With no common columns, you need to decide whether you want to introduce a common column or get the product.
A cartesian product will match every row in the first table with every row in the second.
That''s probably not what you want since 1000 entries in the table 1 and 100 entries in the table 2 would result in 100,000 rows with lots of duplicated information.
Alternatively, you can use a union to just output the data, though not side-by-side (you''ll need to make sure column types are compatible between the two selects, either by making the table columns compatible or coercing them in the select).
If you can be more specific with your question we probably can came up with a valid solution. So, specify if there is a relation between two tables and the number of entries in each one, then we can make a SQL statement that will retrieve the desired result.
Cheers
this way...
with FIND_YRS(yr) as ( select (select convert(int,left('2012-2014',4))) as yr union all select yr + 1 from FIND_YRS where yr < (select convert(int,right('2012-2014',4))) ) select day_Month_Col + '-' + convert(varchar(4),yr) as date from DAY_MONTH_TABLE cross join FIND_YRS
Happy Coding!
:)
Hi,
Check the following Code
-- Table Creations CREATE TABLE #Month (MonthVal VARCHAR(20)) INSERT INTO #Month(MonthVal) VALUES('17-04'),('18-06'),('20-12') CREATE TABLE #Year(YearRange SMALLINT) INSERT INTO #Year(YearRange) VALUES(2012),(2013),(2014) -- Actual Query SELECT MonthVal +'-'+CAST(YearRange AS VARCHAR(5)) FROM #Month,#Year
Regards,
GVPrabu
这篇关于请帮助Sql输出的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文