我在MySQL的查询中遇到问题,我必须获取具有不同"c.email"和不同"pf.client_id"的行,我在下面做这段代码,但我没有得到我想要的,"c.e-mail"不返回distinct
SELECT DISTINCT c.nome as nome, pf.id_cliente as cliente_id
FROM cliente c
INNER JOIN pessoa_fisica pf
ON c.id_cliente = pf.id_cliente
GROUP BY c.email, pf.id_cliente;
我也试过:
SELECT c.nome as nome, pf.id_cliente as cliente_id
FROM cliente c
INNER JOIN pessoa_fisica pf
ON c.id_cliente = pf.id_cliente
GROUP BY c.email, pf.id_cliente;
编辑
cliente=电子邮件(它不是主键,是外键,我已经获得了它)
pessoa_fisica=id_cliente(它不是主键,是外键,我已经获取了它)
这个查询世界对你来说可能是语法错误,因为我没有数据库。
select SUBSTRING_INDEX(full, ' ', 1) AS email ,SUBSTRING_INDEX(full, ' ', -1) AS cliente_id , nome
from
(SELECT DISTINCT (concat(c.email as email,' ',pf.id_cliente )) as 'full' , c.nome as 'nome'
FROM cliente c
INNER JOIN pessoa_fisica pf
ON c.id_cliente = pf.id_cliente) as temp;
Mysql distinct不允许有多个值,但应用少量的工作意味着首先连接列,然后应用distinct,然后再次断开列。我正在为你树立榜样。
Create Table: CREATE TABLE `dupli` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fname` varchar(20) DEFAULT NULL,
`lname` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
insert into dupli (fname,lname) values ('Hitesh', 'mundra'),('Neeraj', 'sharma'),('Kailash','yadav');
insert into dupli (fname,lname) values ('Hitesh', 'mundra'),('Neeraj', 'sharma'),('Kailash','yadav');
select id, SUBSTRING_INDEX(name, ' ', 1) AS fname ,SUBSTRING_INDEX(name, ' ', -1) AS lname from (select distinct( concat(fname," ",lname) ) as Name, id from dupli) as temp;