这是我的存储函数,它从多个表中获取完整的地址
DELIMITER $$
CREATE FUNCTION `get_Address` (Id INT)
RETURNS varchar (250)
BEGIN
DECLARE address varchar (250);
DECLARE state varchar (250);
DECLARE gov varchar (250);
DECLARE city varchar (250);
DECLARE hood varchar (250);
DECLARE street varchar (250);
DECLARE building varchar (250);
DECLARE floor varchar (250);
declare cr1 cursor FOR
SELECT
s.state_Name,
g.governorate_Name,
c.city_Name,
h.hood_Name,
st.street_Name,
a.address_Building,
a.address_Floor
FROM
addresses AS a
INNER JOIN
states AS s ON s.state_Id = a.state_Id
INNER JOIN
governorates AS g ON g.governorate_Id = a.governorate_Id
INNER JOIN
cities AS c ON c.city_Id = a.city_Id
INNER JOIN
hoods AS h ON h.hood_Id = a.hood_Id
INNER JOIN
streets AS st ON st.street_Id = a.street_Id
WHERE
a.address_Id = Id;
OPEN cr1;
FETCH cr1 INTO state, gov, city , hood , street, building ,floor;
CLOSE cur1;
RETURN address;
END我需要这样的东西
address = state + gov + city + hood + street + building + floor;我需要返回所有结果集,用PHP创建完整地址,或者循环结果,在MYSQL中创建完整地址并返回varchar
发布于 2014-05-08 14:15:56
您不能在MySQL中返回结果集,因为我们没有任何这样的数据类型可用。
您可以使用
concat_ws(" ",state , gov , city , hood , street , building , floor) as address 如果您需要它一次,直接在查询中。
如果您要使用这几次,最好是制作一个汇总表。
但这里可能有另一个解决方案:
1)创建临时表
2)获取结果并将其输入临时表。
3)为地址调用tmp表
我的意思是:
DELIMITER $$
DROP PROCEDURE IF EXISTS get_Address$$
CREATE PROCEDURE `get_Address` (IN Id INT)
BEGIN
CREATE TABLE tmp_address(ID INT,address VARCHAR(255));
INSERT INTO tmp_address
SELECT
a.address_Id,
CONCAT_WS(" ",s.state_Name,
g.governorate_Name,
c.city_Name,
h.hood_Name,
st.street_Name,
a.address_Building,
a.address_Floor) as address
FROM
addresses AS a
INNER JOIN
states AS s ON s.state_Id = a.state_Id
INNER JOIN
governorates AS g ON g.governorate_Id = a.governorate_Id
INNER JOIN
cities AS c ON c.city_Id = a.city_Id
INNER JOIN
hoods AS h ON h.hood_Id = a.hood_Id
INNER JOIN
streets AS st ON st.street_Id = a.street_Id
WHERE
a.address_Id = Id;
END$$
DELIMITER ;希望能帮上忙!)
https://stackoverflow.com/questions/23543662
复制相似问题