有如下四个表:
CREATE TABLE `tb_allowphone` ( `id` int(11) NOT NULL AUTO_INCREMENT,
`dealer_id` varchar(200) COLLATE gbk_bin DEFAULT NULL,
`phone` varchar(200) COLLATE gbk_bin DEFAULT NULL,
`groupid` varchar(200) COLLATE gbk_bin DEFAULT NULL,
PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=gbk COLLATE=gbk_bin;
INSERT INTO `tb_allowphone` VALUES ('1', '1', '13480113873', '1');
INSERT INTO `tb_allowphone` VALUES ('2', '1', '13480113874', '1');
INSERT INTO `tb_allowphone` VALUES ('3', '1', '13480113875', '1');
INSERT INTO `tb_allowphone` VALUES ('4', '1', '13480113876', '2');
INSERT INTO `tb_allowphone` VALUES ('5', '1', '13480113873', '2');
INSERT INTO `tb_allowphone` VALUES ('6', '1', '13480113874', '3');
INSERT INTO `tb_allowphone` VALUES ('7', '1', '13480113875', '3');
INSERT INTO `tb_allowphone` VALUES ('8', '1', '13480113876', '3');
CREATE TABLE `tb_phonegroup` (`id` int(11) NOT NULL AUTO_INCREMENT,
`dealer_id` varchar(200) COLLATE gbk_bin DEFAULT NULL,
`groupname` varchar(255) COLLATE gbk_bin DEFAULT NULL,
PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=gbk COLLATE=gbk_bin;INSERT INTO `tb_phonegroup` VALUES ('1', '1', 'A');
INSERT INTO `tb_phonegroup` VALUES ('2', '1', 'B');
INSERT INTO `tb_phonegroup` VALUES ('3', '1', 'C');
INSERT INTO `tb_phonegroup` VALUES ('4', '2', 'D');
INSERT INTO `tb_phonegroup` VALUES ('5', '2', 'E');
INSERT INTO `tb_phonegroup` VALUES ('6', '2', 'F');
CREATE TABLE `tb_vip` (`id` int(10) NOT NULL AUTO_INCREMENT,
`dealer_id` varchar(100) NOT NULL DEFAULT '',
`phone` varchar(20) NOT NULL DEFAULT '',
`name` varchar(20) NOT NULL DEFAULT '',
`age` int(10) DEFAULT '0',
`date` varchar(20) DEFAULT '',
PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=2785 DEFAULT CHARSET=gbk;
INSERT INTO `tb_vip` VALUES ('1', '1','13480113873', 'a', '22, '2009-07-28');
INSERT INTO `tb_vip` VALUES ('2', '1','13480113874','b', '22', '2009-07-28' );
INSERT INTO `tb_vip` VALUES ('3', '1','13480113875', 'c', '22', '2009-07-28');
INSERT INTO `tb_vip` VALUES ('4', '1','13480113876', 'd', '22', '2009-07-28');
INSERT INTO `tb_vip` VALUES ('5', '1','', 'e', '22', '2009-07-28');
INSERT INTO `tb_vip` VALUES ('6', '1','', 'f', '28', '2009-07-28');
CREATE TABLE `tb_clientintergral` (`client_phone` varchar(15) DEFAULT NULL,
`dealer_id` varchar(9) NOT NULL,
`intergral` int(10) NOT NULL,
`vip` varchar(100) DEFAULT '',
KEY `dealer_id` (`dealer_id`)) ENGINE=InnoDB DEFAULT CHARSET=gbk;
INSERT INTO `tb_clientintergral` VALUES ('13480113873', '100', '88203', '');
INSERT INTO `tb_clientintergral` VALUES ('13480113874', '1', '25', '00081000');
INSERT INTO `tb_clientintergral` VALUES ('13480113875', '1', '64696', '');
INSERT INTO `tb_clientintergral` VALUES ('13480113876', '1', '1', '');
INSERT INTO `tb_clientintergral` VALUES ('15814621995', '1', '1', '');
INSERT INTO `tb_clientintergral` VALUES ('15814621959', '1', '10040', '');
INSERT INTO `tb_clientintergral` VALUES ('15814621919', '1', '1', '');
INSERT INTO `tb_clientintergral` VALUES ('15013544524', '1', '2', '');我想得到表tb_allowphone中的所有手机号码(phone),tb_phonegroup表中对应的组名(groupname),tb_vip表中的姓名(name)、年龄(age)、时间(date),tb_clientintergral中的积分(intergral)。
效果如下:
phone groupname name age date intergral
13480113873 A a 22 2009-07-28 100
……………………………………………………
……………………………………………………
tb_allowphone中的手机号在tb_vip表中找不到的手机号也要显示出来
那位高手帮忙,写一个sql语句,谢谢呀!急 急 急 急 急 急 急 急 急 急 急 急 急 …………………………………………………………
CREATE TABLE `tb_allowphone` ( `id` int(11) NOT NULL AUTO_INCREMENT,
`dealer_id` varchar(200) COLLATE gbk_bin DEFAULT NULL,
`phone` varchar(200) COLLATE gbk_bin DEFAULT NULL,
`groupid` varchar(200) COLLATE gbk_bin DEFAULT NULL,
PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=gbk COLLATE=gbk_bin;
INSERT INTO `tb_allowphone` VALUES ('1', '1', '13480113873', '1');
INSERT INTO `tb_allowphone` VALUES ('2', '1', '13480113874', '1');
INSERT INTO `tb_allowphone` VALUES ('3', '1', '13480113875', '1');
INSERT INTO `tb_allowphone` VALUES ('4', '1', '13480113876', '2');
INSERT INTO `tb_allowphone` VALUES ('5', '1', '13480113873', '2');
INSERT INTO `tb_allowphone` VALUES ('6', '1', '13480113874', '3');
INSERT INTO `tb_allowphone` VALUES ('7', '1', '13480113875', '3');
INSERT INTO `tb_allowphone` VALUES ('8', '1', '13480113876', '3');
CREATE TABLE `tb_phonegroup` (`id` int(11) NOT NULL AUTO_INCREMENT,
`dealer_id` varchar(200) COLLATE gbk_bin DEFAULT NULL,
`groupname` varchar(255) COLLATE gbk_bin DEFAULT NULL,
PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=gbk COLLATE=gbk_bin;INSERT INTO `tb_phonegroup` VALUES ('1', '1', 'A');
INSERT INTO `tb_phonegroup` VALUES ('2', '1', 'B');
INSERT INTO `tb_phonegroup` VALUES ('3', '1', 'C');
INSERT INTO `tb_phonegroup` VALUES ('4', '2', 'D');
INSERT INTO `tb_phonegroup` VALUES ('5', '2', 'E');
INSERT INTO `tb_phonegroup` VALUES ('6', '2', 'F');
CREATE TABLE `tb_vip` (`id` int(10) NOT NULL AUTO_INCREMENT,
`dealer_id` varchar(100) NOT NULL DEFAULT '',
`phone` varchar(20) NOT NULL DEFAULT '',
`name` varchar(20) NOT NULL DEFAULT '',
`age` int(10) DEFAULT '0',
`date` varchar(20) DEFAULT '',
PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=2785 DEFAULT CHARSET=gbk;
INSERT INTO `tb_vip` VALUES ('1', '1','13480113873', 'a', '22, '2009-07-28');
INSERT INTO `tb_vip` VALUES ('2', '1','13480113874','b', '22', '2009-07-28' );
INSERT INTO `tb_vip` VALUES ('3', '1','13480113875', 'c', '22', '2009-07-28');
INSERT INTO `tb_vip` VALUES ('4', '1','13480113876', 'd', '22', '2009-07-28');
INSERT INTO `tb_vip` VALUES ('5', '1','', 'e', '22', '2009-07-28');
INSERT INTO `tb_vip` VALUES ('6', '1','', 'f', '28', '2009-07-28');
CREATE TABLE `tb_clientintergral` (`client_phone` varchar(15) DEFAULT NULL,
`dealer_id` varchar(9) NOT NULL,
`intergral` int(10) NOT NULL,
`vip` varchar(100) DEFAULT '',
KEY `dealer_id` (`dealer_id`)) ENGINE=InnoDB DEFAULT CHARSET=gbk;
INSERT INTO `tb_clientintergral` VALUES ('13480113873', '100', '88203', '');
INSERT INTO `tb_clientintergral` VALUES ('13480113874', '1', '25', '00081000');
INSERT INTO `tb_clientintergral` VALUES ('13480113875', '1', '64696', '');
INSERT INTO `tb_clientintergral` VALUES ('13480113876', '1', '1', '');
INSERT INTO `tb_clientintergral` VALUES ('15814621995', '1', '1', '');
INSERT INTO `tb_clientintergral` VALUES ('15814621959', '1', '10040', '');
INSERT INTO `tb_clientintergral` VALUES ('15814621919', '1', '1', '');
INSERT INTO `tb_clientintergral` VALUES ('15013544524', '1', '2', '');我想得到表tb_allowphone中的所有手机号码(phone),tb_phonegroup表中对应的组名(groupname),tb_vip表中的姓名(name)、年龄(age)、时间(date),tb_clientintergral中的积分(intergral)。
效果如下:
phone groupname name age date intergral
13480113873 A a 22 2009-07-28 100
……………………………………………………
……………………………………………………
tb_allowphone中的手机号在tb_vip表中找不到的手机号也要显示出来
那位高手帮忙,写一个sql语句,谢谢呀!急 急 急 急 急 急 急 急 急 急 急 急 急 …………………………………………………………
select
a.phone,
b.groupname,
c.name,
c.age,
c.`date`,
d.intergral
from
tb_allowphone a
left join
tb_phonegroup b on a.groupid=b.id
left join
tb_vip c on a.phone=c.phone
left join
tb_clientintergral d on a.phone=d.client_phone/**
phone groupname name age date intergral
13480113873 A a 22 2009-07-28 88203
13480113874 A b 22 2009-07-28 25
13480113875 A c 22 2009-07-28 64696
13480113876 B d 22 2009-07-28 1
13480113873 B a 22 2009-07-28 88203
13480113874 C b 22 2009-07-28 25
13480113875 C c 22 2009-07-28 64696
13480113876 C d 22 2009-07-28 1
**/
-> from tb_allowphone a left join tb_phonegroup p on a.groupid=p.id
-> left join tb_vip v on a.id=v.id
-> left join tb_clientintergral c on a.phone=c.client_phone;
+-------------+-----------+------+------+------------+-----------+
| phone | groupname | name | age | date | intergral |
+-------------+-----------+------+------+------------+-----------+
| 13480113873 | A | a | 22 | 2009-07-28 | 88203 |
| 13480113874 | A | b | 22 | 2009-07-28 | 25 |
| 13480113875 | A | c | 22 | 2009-07-28 | 64696 |
| 13480113876 | B | d | 22 | 2009-07-28 | 1 |
| 13480113873 | B | e | 22 | 2009-07-28 | 88203 |
| 13480113874 | C | f | 28 | 2009-07-28 | 25 |
| 13480113875 | C | NULL | NULL | NULL | 64696 |
| 13480113876 | C | NULL | NULL | NULL | 1 |
+-------------+-----------+------+------+------------+-----------+
8 rows in set (0.00 sec)mysql>
不知道哪种是对的,并且也得不出你的那个
phone groupname name age date intergral
13480113873 A a 22 2009-07-28 100建议楼主能给出针对你提供的测试数据的正确结果。否则大家只能猜了。
直接左联接关联就可以得到了,想上面2位写的语句那样还有,你给的测试结果自己都写错了,看了下你的记录,根本就没有对应记录phone groupname name age date intergral
13480113873 A a 22 2009-07-28 100 ,应该为88203
老兄,你的语名少了一个group by a.id
谢谢哦!你的结果是最接近我想要的效果。
SELECT a.phone, b.groupname, c.Name, c.Age, c.Date, d.Intergral FROM tb_allowphone a
LEFT JOIN tb_phonegroup b ON a.groupid=b.id
LEFT JOIN tb_vip c ON a.phone=c.phone
LEFT JOIN tb_clientintergral d ON a.phone=d.client_phoneresult:phone groupname Name Age Date Intergral
13480113873 A a 22 2009-07-28 88203
13480113874 A b 22 2009-07-28 25
13480113875 A c 22 2009-07-28 64696
13480113876 B d 22 2009-07-28 1
13480113873 B a 22 2009-07-28 88203
13480113874 C b 22 2009-07-28 25
13480113875 C c 22 2009-07-28 64696
13480113876 C d 22 2009-07-28 1/*--------------------------------------------*/
又用你所说的"你的语名少了一个group by a.id "SELECT a.phone, b.groupname, c.Name, c.Age, c.Date, d.Intergral FROM tb_allowphone a
LEFT JOIN tb_phonegroup b ON a.groupid=b.id
LEFT JOIN tb_vip c ON a.phone=c.phone
LEFT JOIN tb_clientintergral d ON a.phone=d.client_phone
group by a.idresult:
phone groupname Name Age Date Intergral
13480113873 A a 22 2009-07-28 88203
13480113874 A b 22 2009-07-28 25
13480113875 A c 22 2009-07-28 64696
13480113876 B d 22 2009-07-28 1
13480113873 B a 22 2009-07-28 88203
13480113874 C b 22 2009-07-28 25
13480113875 C c 22 2009-07-28 64696
13480113876 C d 22 2009-07-28 1发现都符合你的要求啊,是不是你的需求没表达清楚呢?
另外,3楼的关联条件“tb_vip v on a.id=v.id”这部分应该不符合