avatar
mysql 问题 (转载)# Database - 数据库
j*3
1
【 以下文字转载自 Statistics 讨论区 】
发信人: j1123 (2134), 信区: Statistics
标 题: mysql 问题
发信站: BBS 未名空间站 (Sun Jan 3 17:15:03 2010, 美东)
table 1
categories location
A M
A H
B M
B H
C M
C H



table 2
location categories_A categories_B categories_C 。。。。。
M 100 300 500
H 300 700 109



想从 table 1 和 table 2 得到
table 3
categories location
avatar
y*w
2
select categories, location,
(
select case categories when 'A' then categories_a when 'B' then categories_b
when 'C' then categories_c end
from table2 where location = tb1.location
)
from table1 tb1
;
try whether it works in mysql.
avatar
a9
3
SELECT a.categories,a.location ,CASE a.categories WHEN 'A' THEN b.categorie
s_a WHEN 'B' THEN b.categories_b WHEN 'C' THEN b.categories_C END as
categories
FROM table1 LEFT JOIN table2 as b ON a.location=b.location
这样就行吧?

_b

【在 y****w 的大作中提到】
: select categories, location,
: (
: select case categories when 'A' then categories_a when 'B' then categories_b
: when 'C' then categories_c end
: from table2 where location = tb1.location
: )
: from table1 tb1
: ;
: try whether it works in mysql.

avatar
y*w
4
等价的,习惯问题,

categorie

【在 a9 的大作中提到】
: SELECT a.categories,a.location ,CASE a.categories WHEN 'A' THEN b.categorie
: s_a WHEN 'B' THEN b.categories_b WHEN 'C' THEN b.categories_C END as
: categories
: FROM table1 LEFT JOIN table2 as b ON a.location=b.location
: 这样就行吧?
:
: _b

avatar
j*3
5
谢谢ls的a9和yhangw!
两个table的categories比较变态,数量》100,这样一个个列太慢,请问有没有可能用个变量表
示table 1 中的categories?
再谢了
avatar
B*g
6
I believe this can be done through XML functions. Too busy, no time to write
real code now. You can try.

用个变量表

【在 j***3 的大作中提到】
: 谢谢ls的a9和yhangw!
: 两个table的categories比较变态,数量》100,这样一个个列太慢,请问有没有可能用个变量表
: 示table 1 中的categories?
: 再谢了

avatar
k*0
7
metabase could work. Just an idea:
could be
select *
from some_systemtable
where column like 'categories_%'
then ... make sql command strings ... could use cursor too (not sure if
mysql has it, should have similars)
avatar
y*w
8
看来你被那个xml function大拿给诱惑了,呵呵,

write

【在 B*****g 的大作中提到】
: I believe this can be done through XML functions. Too busy, no time to write
: real code now. You can try.
:
: 用个变量表

avatar
y*w
9
a9那种写法比我的习惯好,呵呵,对有些不怎么聪明的优化器来说我那种效率
可能会差不少,
to lz,用下面这个自动生成目标sql语句,
mysql> select * from t1;
+------------+----------+
| catagories | location |
+------------+----------+
| A | M |
| A | H |
| B | M |
| B | H |
| C | M |
| C | H |
+------------+----------+
6 rows in set (0.00 sec)
mysql> select concat('select a.categories, a.location, ', group_concat(distinct
concat('case when categories = ''', catago
相关阅读
logo
联系我们隐私协议©2024 redian.news
Redian新闻
Redian.news刊载任何文章,不代表同意其说法或描述,仅为提供更多信息,也不构成任何建议。文章信息的合法性及真实性由其作者负责,与Redian.news及其运营公司无关。欢迎投稿,如发现稿件侵权,或作者不愿在本网发表文章,请版权拥有者通知本网处理。