c***n
发帖数: 921 | 1 请教2个sql query 问题:
1. 假设有table A, and A has attribute k1, k2, k3, k4, k5
怎样判断{k1, k2, k3} 是否是一个candidate key? 也就是说是否{k1, k2, k3}
uniquely identify each row.
2. 假设有table A, and A has a numeric attribute "date". It stores
information like "200604", which means April in year 2006. Now I want to
derive a new table B, which contains two numerical attribute "Month" and "
year" from A. How to write the query?
也就是说从原来的 200604 变成 2006 和 4, 都是numeric attribute.
谢谢看完我的问题. | B*****g
发帖数: 34098 | 2 1. http://www.w3schools.com/sql/sql_groupby.asp
2. substr/substring
【在 c***n 的大作中提到】
: 请教2个sql query 问题:
: 1. 假设有table A, and A has attribute k1, k2, k3, k4, k5
: 怎样判断{k1, k2, k3} 是否是一个candidate key? 也就是说是否{k1, k2, k3}
: uniquely identify each row.
: 2. 假设有table A, and A has a numeric attribute "date". It stores
: information like "200604", which means April in year 2006. Now I want to
: derive a new table B, which contains two numerical attribute "Month" and "
: year" from A. How to write the query?
: 也就是说从原来的 200604 变成 2006 和 4, 都是numeric attribute.
: 谢谢看完我的问题.
| j*****n
发帖数: 1781 | 3 T-SQL:
IF EXISTS(
SELECT k1, k2, k3 FROM A
GROUP BY k1,k2,k3
Having count(1) > 1
)
PRINT 'Not a candidate key'
ELSE
PRINT 'Yes, it is'
INSERT INTO B (Month, Year)
SELECT RIGHT(Convert(varchar(6), date), 2) Month,
LEFT(Convert(varchar(6), date), 4) Year
FROM A
【在 c***n 的大作中提到】
: 请教2个sql query 问题:
: 1. 假设有table A, and A has attribute k1, k2, k3, k4, k5
: 怎样判断{k1, k2, k3} 是否是一个candidate key? 也就是说是否{k1, k2, k3}
: uniquely identify each row.
: 2. 假设有table A, and A has a numeric attribute "date". It stores
: information like "200604", which means April in year 2006. Now I want to
: derive a new table B, which contains two numerical attribute "Month" and "
: year" from A. How to write the query?
: 也就是说从原来的 200604 变成 2006 和 4, 都是numeric attribute.
: 谢谢看完我的问题.
| c***n
发帖数: 921 | 4 T-SQL 应该和 PL/SQL 一回事吧. 我用的是db2, 八成语法差不多. 让我查查看.
【在 j*****n 的大作中提到】
: T-SQL:
:
: IF EXISTS(
: SELECT k1, k2, k3 FROM A
: GROUP BY k1,k2,k3
: Having count(1) > 1
: )
: PRINT 'Not a candidate key'
: ELSE
: PRINT 'Yes, it is'
| j*****n
发帖数: 1781 | 5 嗯，差不多，尤其 2005 搞过 Oracle 的就能搞。
【在 c***n 的大作中提到】
: T-SQL 应该和 PL/SQL 一回事吧. 我用的是db2, 八成语法差不多. 让我查查看.
| c***n
发帖数: 921 | 6 I found another method. It works well.
insert INTO B (MONTH, YEAR)
select mod(date,100),date/100
FROM A
【在 j*****n 的大作中提到】
: T-SQL:
:
: IF EXISTS(
: SELECT k1, k2, k3 FROM A
: GROUP BY k1,k2,k3
: Having count(1) > 1
: )
: PRINT 'Not a candidate key'
: ELSE
: PRINT 'Yes, it is'
|
|
