问题如下:
表A
CREATE TABLE student_new
(
pkey1 int4 NOT NULL,
pkey2 varchar(20) NOT NULL,
attend_year int4,
attend_grade int4,
attend_class text,
attend_no int4,
former_year int4,
former_grade int4,
former_class text,
former_no int4,
CONSTRAINT student_new_pkey PRIMARY KEY (pkey1, pkey2)
) 例: 表A1:pkey1 pkey2 attend_year attend_grade attend_class attend_no attend_year attend_grade attend_class attend_no
1 BMW 2008 3 班级3 3 2007 2 班级2 2 要求查询结果如下:
pkey1 pkey2 year grade class no
1 BMW 2008 3 班级3 3
1 BMW 2007 2 班级2 2
谢谢!
表A
CREATE TABLE student_new
(
pkey1 int4 NOT NULL,
pkey2 varchar(20) NOT NULL,
attend_year int4,
attend_grade int4,
attend_class text,
attend_no int4,
former_year int4,
former_grade int4,
former_class text,
former_no int4,
CONSTRAINT student_new_pkey PRIMARY KEY (pkey1, pkey2)
) 例: 表A1:pkey1 pkey2 attend_year attend_grade attend_class attend_no attend_year attend_grade attend_class attend_no
1 BMW 2008 3 班级3 3 2007 2 班级2 2 要求查询结果如下:
pkey1 pkey2 year grade class no
1 BMW 2008 3 班级3 3
1 BMW 2007 2 班级2 2
谢谢!
TRY:
SELECT pkey1,pkey2,attend_year,attend_grade,attend_class,attend_no
FROM TT WHERE attend_year='2008'
UNION
SELECT pkey1,pkey2,attend_year,attend_grade,attend_class,attend_no
FROM TT WHERE attend_year='2007'
例: 表A1:pkey1 pkey2 attend_year attend_grade attend_class attend_no former_year former_grade former_class former_no
1 BMW 2008 3 班级3 3 2007 2 班级2 2
要求查询结果如下:
pkey1 pkey2 year grade class no
1 BMW 2008 3 班级3 3
1 BMW 2007 2 班级2 2
SELECT pkey1,pkey2,attend_year,attend_grade,attend_class,attend_no
FROM TT WHERE attend_year='2008'
UNION
SELECT pkey1,pkey2,former_year,former_grade,former_class,former_no
FROM TT WHERE attend_year='2007'
order by pkey1,pkey2,attend_year,attend_grade
通过表结构可以看出:attend_year, attend_grade, attend_class, attend_no和former_year,former_grade,former_class,former_no 其实类型对应是一样的,我要的其实就是主键一样的情况下,一条记录拆分成两条记录的一个检索结果,不知道您明白了我的意思没有?
能不能给一个例子?一定要是多列啦,谢谢了。
示例:
select * from a left join b on a.id=b.id
表A
CREATE TABLE student_new
(
pkey1 int4 NOT NULL,
pkey2 varchar(20) NOT NULL,
attend_year int4,
attend_grade int4,
attend_class text,
attend_no int4
CONSTRAINT student_new_pkey PRIMARY KEY (pkey1, pkey2)
)表B
CREATE TABLE grade_tbl
(
pkey1 int4 NOT NULL,
pkey2 text NOT NULL,
attend_year int4 NOT NULL,
attend_grade int4 NOT NULL,
attend_class text NOT NULL,
attend_no int4 NOT NULL,
CONSTRAINT table1_pkey PRIMARY KEY (pkey1,pkey2 , attend_year)
)
例: 表A: pkey1 pkey2 attend_year attend_grade attend_class attend_no
1 BMW 2008 3 班级3 3
把A表的这个记录更新到表B
谢谢!
UPDATE B SET (pkey1,pkey2,attend_year,attend_grade,attend_class,attend_no)=
(A.pkey1,A.pkey2,A.attend_year,A.attend_grade,A.attend_class,A.attend_no)
FROM A WHERE A.pkey1=B.pkey1postgresql不是很熟悉,可以查查postgresql的UPDATE语法
我做了一个成功了:
update B set attend_year=A1.attend_year, attend_grade=A1.attend_grade,
attend_class=A1.attend_class,attend_no=A1.attend_no
from (select pkey1, pkey2,attend_year,attend_grade,attend_class,attend_no
from A where pkey1=1
union
select pkey1, pkey2,former_year,former_grade,former_class,former_no
from A
where pkey1=1 ) as A1
where A1.pkey1 = grade_tbl.sch_id and A1.pkey2 = B.pkey2
and A1.pkey2 in (select pkey2 from B where pkey1 = 1)
我再试试