如何获取每列的第一行和最后一行

Question

输入

DATE    TRANSACTION    TYPE
may01      22           ATM
jun18      34           ATM
Aug14      38           NB
jul18      46           NB
Sep11      29           NB
Dec21      70           NB
jan02      46           MobileB
Jun19      20           MobileB
Sep13      81           MobileB

如何获取每列的第一行和最后一行，例如：

TYPE        Start_DATE   End_DATE
ATM          may01       jun18
NB           Aug14       Dec21
MobileB      jan02       Sep13 

在此输出中，需要按类型分组，获取第一个日期和最后一个日期。请帮助我。

- suri5913

Answer 1

在你大规模修改问题之前，我建议：

SELECT 'COL1' AS `COL_NAMES`,(SELECT `c1` as `START_ROW` FROM `tbl` WHERE !ISNULL(`c1`) ORDER BY c1 ASC LIMIT 1) AS `ROW_START`,(SELECT `c1` as `END_ROW` FROM `tbl` WHERE !ISNULL(`c1`) ORDER BY c1 DESC LIMIT 1) AS `ROW_END`
UNION ALL
SELECT 'COL2' AS `COL_NAMES`,(SELECT `c2` as `START_ROW` FROM `tbl` WHERE !ISNULL(`c2`) ORDER BY c2 ASC LIMIT 1) AS `ROW_START`,(SELECT `c2` as `END_ROW` FROM `tbl` WHERE !ISNULL(`c2`) ORDER BY c2 DESC LIMIT 1) AS `ROW_END`
UNION ALL
SELECT 'COL3' AS `COL_NAMES`,(SELECT `c3` as `START_ROW` FROM `tbl` WHERE !ISNULL(`c3`) ORDER BY c3 ASC LIMIT 1) AS `ROW_START`,(SELECT `c3` as `END_ROW` FROM `tbl` WHERE !ISNULL(`c3`) ORDER BY c3 DESC LIMIT 1) AS `ROW_END`
UNION ALL
SELECT 'COL4' AS `COL_NAMES`,(SELECT `c4` as `START_ROW` FROM `tbl` WHERE !ISNULL(`c4`) ORDER BY c4 ASC LIMIT 1) AS `ROW_START`,(SELECT `c4` as `END_ROW` FROM `tbl` WHERE !ISNULL(`c4`) ORDER BY c4 DESC LIMIT 1) AS `ROW_END`

假设有如下表格：

CREATE TABLE IF NOT EXISTS `tbl` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  `c4` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `tbl` (`c1`, `c2`, `c3`, `c4`) VALUES
(1, 30, 89, 34),
(2, 49, 76, 44),
(NULL, 52, 90, NULL),
(NULL, NULL, 16, NULL);

我现在无法给你一个确切的答案，因为有了你的新数据。但是我的回答应该会让你非常接近答案，只需要修复一些东西，比如列名，表名。如果你正在测试第一个和最后一个非NULL值，包括空字符串，则我的！ISNULL（...）逻辑是正确的。如果你想测试空字符串，请改用LENGTH（...）= 0，或两者都使用： （！ISNULL（...）AND LENGTH（...）> 0）。

- Ultimater

Answer 2

你可以使用 GROUP_CONCAT 和 SUBSTRING_INDEX 来获取第一行和最后一行，如下所示。

select type,
SUBSTRING_INDEX(GROUP_CONCAT(CAST(date AS CHAR) ORDER BY date), ',', 1 ) as Start_date,
SUBSTRING_INDEX(GROUP_CONCAT(CAST(date AS CHAR) ORDER BY date DESC), ',', 1 ) as End_date
from test
group by type;

请在这里查看SQL Fiddle DEMO。

- Arun Palanisamy

---

很高兴能帮到您...如果我的回答对您有帮助，请友善地接受它，这样其他人也可以受益 :) - Arun Palanisamy

Answer 3

以下代码运行良好，首先我将日期转换为日期格式 select*，str_to_date(date，“%m /%d /%Y”）as dat from tab3;

SELECT TYPE，MIN（DAT）AS SD，MAX（DAT）AS ED FROM A GROUP BY TYPE;

- suri5913

Answer 4

在SQL中有预定义的函数，用于根据列名提取第一行和最后一行记录

SELECT FIRST(column_name) FROM table_name;
SELECT LAST(column_name) FROM table_name; 

在MySQL中

SELECT column_name FROM table_name
ORDER BY column_name ASC
LIMIT 1;



 SELECT column_name FROM table_name
    ORDER BY column_name DESC
    LIMIT 1;

- Siddhartha esunuri

---

这个函数在MySQL中真的可用吗？如果是，那么它在哪个版本的MySQL中可用？ - Vishal JAIN

以上代码适用于SQL。如果您想要在MySQL中使用，请执行以下操作：SELECT column_name FROM table_name ORDER BY column_name ASC LIMIT 1; - Siddhartha esunuri

Answer 5

select
*
from
(select * from application, (SELECT @rownums:=0) r order by  (@rownum := @rownum + 1) asc limit 1) a1

union

select
*
from
(select * from application, (SELECT @rownum:=0) r1 order by  (@rownum := @rownum + 1) desc limit 1) a2;

- Z.I.J