Hjaelp til SQL saetning

Jeg ved godt ovenstaaende resultat ikke passer til tabellens data, men det er blot for at give et eksempel.. PS. Det er Oracle 10g XE

Hej baitianlong,

Hvis det nu var Oracle 11 kunne du måske bruge det nye PIVOT keyword:
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-pivot.html
Der er dog ikke mange som bruge Oracle 11 endnu, og det har jeg endnu også til gode.

I pre-Oracle 11 SQL kan du overordnet set gøre det på to måder. Ulempen ved begge har alle dage været at man på query/SQL design stadiet beslutter hvor mange kolonner SQL'en skal kunne håndtere. Så hvis antallet af kolonner skal være dynamisk skal man i pre-Oracle 11 enten bruge et andet værktøj som fx. Oracle Reports eller Excel - eller hvis det skal være i basen, en PL/SQL table function (evt. pipelined) som læser fra tabellen og returnerer informationerne række for række i det ønskede format i en varchar2-streng.

Det står sikkert grimt - så kopier det ind i en Notepad:
SELECT username, sum( last1day) last1day, sum( last2days) last2days, sum( last3days) last3days
  FROM (SELECT username, count(*) AS last1day, 0 AS last2days, 0 last3days FROM userstamp WHERE entrydate > (sysdate-1) GROUP BY username
        UNION ALL
        SELECT username, 0 AS last1day, count(*) AS last2days, 0 last3days FROM userstamp WHERE entrydate > (sysdate-2) GROUP BY username
        UNION ALL
        SELECT username, 0 AS last1day, 0 AS last2days, count(*) last3days FROM userstamp WHERE entrydate > (sysdate-3) GROUP BY username
        )
GROUP BY username
ORDER BY username;

SELECT username,
      sum( CASE WHEN entrydate > (sysdate-1) THEN 1 ELSE 0 END) last1day,
      sum( CASE WHEN entrydate > (sysdate-2) THEN 1 ELSE 0 END) last2days,
      sum( CASE WHEN entrydate > (sysdate-3) THEN 1 ELSE 0 END) last3days
  FROM userstamp
GROUP BY username
ORDER BY username;

Hejsa,

Mange tak for din hjaelp. Der er blot eet problem og det er at din sql laegger tallene sammen. Maaske var jeg ikke klar nok i min forklaring. Det beklager jeg. Men det skal vaere saa-og-saa-mange i gaar, saa-og-saa-mange i forgaars osv.

Haaber dette giver mening...

Altsaa min last2days er ogsaa daarligt valgt af mig. Jeg burde have skrevet 'yesterday','2 days ago','3 days ago'

Jeg mener også at konceptet med days 1, 2, 3 som kolonner er grimt.

Den relationelle måde må være at have en kolonne med antal dage.

Eksempel:

CREATE TABLE log (
    id INTEGER NOT NULL,
    username VARCHAR2(32),
    entrydate DATE,
    PRIMARY KEY(id)
);

INSERT INTO log VALUES(1,'A', TO_DATE('05-09-2008 17:10', 'DD-MM-YYYY HH24:MI'));
INSERT INTO log VALUES(2,'A', TO_DATE('05-09-2008 20:30', 'DD-MM-YYYY HH24:MI'));
INSERT INTO log VALUES(3,'B', TO_DATE('05-09-2008 23:00', 'DD-MM-YYYY HH24:MI'));
INSERT INTO log VALUES(4,'A', TO_DATE('06-09-2008 15:20', 'DD-MM-YYYY HH24:MI'));

SELECT username,TRUNC(SYSDATE)-TRUNC(entrydate)+1 AS days,COUNT(entrydate) AS records
FROM log
GROUP BY username,TRUNC(SYSDATE)-TRUNC(entrydate)+1
HAVING TRUNC(SYSDATE)-TRUNC(entrydate)+1 <= 3
ORDER BY TRUNC(SYSDATE)-TRUNC(entrydate)+1,username;

SELECT t1.username AS username,t1.days AS days,NVL(t2.records,0) AS records
FROM (SELECT t1username.username AS username,t1days.days AS days
    FROM (SELECT DISTINCT username FROM log) t1username,
          (SELECT ROWNUM AS days FROM all_objects WHERE ROWNUM <= 3) t1days) t1 LEFT JOIN
(SELECT username,TRUNC(SYSDATE)-TRUNC(entrydate)+1 AS days,COUNT(entrydate) AS records
FROM log
GROUP BY username,TRUNC(SYSDATE)-TRUNC(entrydate)+1
HAVING TRUNC(SYSDATE)-TRUNC(entrydate)+1 <= 3) t2
ON t1.username=t2.username AND t1.days=t2.days
ORDER BY t1.days,t1.username;

DROP TABLE log;

Den første variant giver:

USERNAME                              DAYS    RECORDS
-------------------------------- ---------- ----------
A                                        1          1
A                                        2          2
B                                        2          1

Den anden variant giver:

USERNAME                              DAYS    RECORDS
-------------------------------- ---------- ----------
A                                        1          1
B                                        1          0
A                                        2          2
B                                        2          1
A                                        3          0
B                                        3          0

Jamen så lave du bare WHERE clause'n i SQL1 eller CASE betingelserne i SQL2 om. I dit tilfælde er det måske SQL2 som er mest enkel:
SELECT username,
      sum( CASE WHEN (sysdate-1) <= entrydate                            THEN 1 ELSE 0 END) last1day,
      sum( CASE WHEN (sysdate-2) <= entrydate AND entrydate < (sysdate-1) THEN 1 ELSE 0 END) last2days,
      sum( CASE WHEN (sysdate-3) <= entrydate AND entrydate < (sysdate-2) THEN 1 ELSE 0 END) last3days
  FROM userstamp
GROUP BY username
ORDER BY username;

I SQL-mæssig forstand har arve_v ret. Rapporteringsmæssigt laver man dog ofte en pivot da det giver det overblik forretningen skal have.

Det skal bruges til at se hvor mange kommentarer, der har vaeret paa forskellige blogs de sidste 5 dage. Derfor er stebers loesning glimrende. Du har dog glemt ordet AS et par steder i sql'en, tror jeg. Dem har jeg dog selv puttet ind, og det virker fint. Den ser saadan her ud nu:

SELECT blogname, sum( last1day) last1day, sum( last2days) last2days, sum( last3days) last3days, sum( last4days ) last4days, sum( last5days ) last5days
  FROM (SELECT blogname, count(*) AS last1day, 0 AS last2days, 0 AS last3days, 0 AS last4days, 0 AS last5days FROM blogcomment WHERE commentdate > (sysdate-1) GROUP BY blogname
        UNION ALL
        SELECT blogname, 0 AS last1day, count(*) AS last2days, 0 AS last3days, 0 AS last4days, 0 AS last5days FROM blogcomment WHERE commentdate > (sysdate-2) GROUP BY blogname
        UNION ALL
        SELECT blogname, 0 AS last1day, 0 AS last2days, count(*) AS last3days, 0 AS last4days, 0 AS last5days FROM blogcomment WHERE commentdate > (sysdate-3) GROUP BY blogname
        UNION ALL
        SELECT blogname, 0 AS last1day, 0 AS last2days, 0 AS last3days, count(*) AS last4days, 0 AS last5days FROM blogcomment WHERE commentdate > (sysdate-4) GROUP BY blogname
        UNION ALL
        SELECT blogname, 0 AS last1day, 0 AS last2days, 0 AS last3days, 0 AS last4days, count(*) AS last5days FROM blogcomment WHERE commentdate > (sysdate-5) GROUP BY blogname
    UNION ALL
        )
GROUP BY blogname
ORDER BY blogname

SELECT blogname,
      sum( CASE WHEN (sysdate-1) <= commentdate THEN 1 ELSE 0 END) last1day,
      sum( CASE WHEN (sysdate-2) <= commentdate AND commentdate < (sysdate-1) THEN 1 ELSE 0 END) last2days,
      sum( CASE WHEN (sysdate-3) <= commentdate AND commentdate < (sysdate-2) THEN 1 ELSE 0 END) last3days,
      sum( CASE WHEN (sysdate-4) <= commentdate AND commentdate < (sysdate-3) THEN 1 ELSE 0 END) last4days,
      sum( CASE WHEN (sysdate-5) <= commentdate AND commentdate < (sysdate-4) THEN 1 ELSE 0 END) last5days
  FROM blogcomment
GROUP BY blogname
ORDER BY blogname;

Skal der staa UNION ALL efter sidste sub-select, som der goer efter de foerste? Det lader ikke til at goere den store forskel om det er der eller ej?

Mange tak for denne heftige sql saetning, hvis du laegger et svar (evt. kommenterer paa det med UNION ALL) kan vi lukke her :)

Er der een af jeg, der har en ide om hvorfor Oracle siger

java.sql.SQLException: ORA-00928: missing SELECT keyword

Naar jeg putter sql saetningen ind i en metode.

Metoden ser saadan her ud:

public static List<BlogActivity> getBlogActivities(Connection con) {

        List<BlogActivity> blogactivities = new ArrayList<BlogActivity>();

        try {
            Statement stmt = con.createStatement();
            String sql = "SELECT blogname, sum( last1day) last1day, sum( last2days) last2days, sum( last3days) last3days, sum( last4days ) last4days, sum( last5days ) last5days\n" +
                    "  FROM (SELECT blogname, count(*) AS last1day, 0 AS last2days, 0 AS last3days, 0 AS last4days, 0 AS last5days FROM blogcomment WHERE commentdate > (sysdate-1) GROUP BY blogname\n" +
                    "        UNION ALL\n" +
                    "        SELECT blogname, 0 AS last1day, count(*) AS last2days, 0 AS last3days, 0 AS last4days, 0 AS last5days FROM blogcomment WHERE commentdate > (sysdate-2) GROUP BY blogname\n" +
                    "        UNION ALL\n" +
                    "        SELECT blogname, 0 AS last1day, 0 AS last2days, count(*) AS last3days, 0 AS last4days, 0 AS last5days FROM blogcomment WHERE commentdate > (sysdate-3) GROUP BY blogname\n" +
                    "        UNION ALL\n" +
                    "        SELECT blogname, 0 AS last1day, 0 AS last2days, 0 AS last3days, count(*) AS last4days, 0 AS last5days FROM blogcomment WHERE commentdate > (sysdate-4) GROUP BY blogname\n" +
                    "        UNION ALL\n" +
                    "        SELECT blogname, 0 AS last1day, 0 AS last2days, 0 AS last3days, 0 AS last4days, count(*) AS last5days FROM blogcomment WHERE commentdate > (sysdate-5) GROUP BY blogname\n" +
                    "        UNION ALL\n" +
                    "        )\n" +
                    "GROUP BY blogname\n" +
                    "ORDER BY blogname\n" +
                    "\n" +
                    "SELECT blogname,\n" +
                    "      sum( CASE WHEN (sysdate-1) <= commentdate THEN 1 ELSE 0 END) last1day,\n" +
                    "      sum( CASE WHEN (sysdate-2) <= commentdate AND commentdate < (sysdate-1) THEN 1 ELSE 0 END) last2days,\n" +
                    "      sum( CASE WHEN (sysdate-3) <= commentdate AND commentdate < (sysdate-2) THEN 1 ELSE 0 END) last3days,\n" +
                    "      sum( CASE WHEN (sysdate-4) <= commentdate AND commentdate < (sysdate-3) THEN 1 ELSE 0 END) last4days,\n" +
                    "      sum( CASE WHEN (sysdate-5) <= commentdate AND commentdate < (sysdate-4) THEN 1 ELSE 0 END) last5days\n" +
                    "  FROM blogcomment\n" +
                    "GROUP BY blogname\n" +
                    "ORDER BY blogname";
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()) {
                BlogActivity ba = new BlogActivity(rs.getString("blogname"));
                ba.setItemssminus1days(rs.getInt("last1day"));
                ba.setItemssminus2days(rs.getInt("last2days"));
                ba.setItemssminus3days(rs.getInt("last3days"));
                ba.setItemssminus4days(rs.getInt("last4days"));
                ba.setItemssminus5days(rs.getInt("last5days"));
                blogactivities.add(ba);
            }
        } catch (SQLException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
        return blogactivities;
    }

Du har tilsyneladende 2 SQL sætninger i samme sætning - det duer ikke.

Og så tror jeg også at du skal fjerne \n.

Jeg viste to forskellige SQL'er fordi det er to principielt forskellige metoder at gøre det samme på. Du skal kun bruge en af dem :-)

Men de giver forskellige resultater:

SQL> SELECT blogname, sum( last1day) last1day, sum( last2days) last2days, sum( l
ast3days) last3days, sum( last4days ) last4days, sum( last5days ) last5days
  2    FROM (SELECT blogname, count(*) AS last1day, 0 AS last2days, 0 AS last3da
ys, 0 AS last4days, 0 AS last5days FROM blogcomment WHERE commentdate > (sysdate
-1) GROUP BY blogname
  3          UNION ALL
  4          SELECT blogname, 0 AS last1day, count(*) AS last2days, 0 AS last3da
ys, 0 AS last4days, 0 AS last5days FROM blogcomment WHERE commentdate > (sysdate
-2) GROUP BY blogname
  5          UNION ALL
  6          SELECT blogname, 0 AS last1day, 0 AS last2days, count(*) AS last3da
ys, 0 AS last4days, 0 AS last5days FROM blogcomment WHERE commentdate > (sysdate
-3) GROUP BY blogname
  7          UNION ALL
  8          SELECT blogname, 0 AS last1day, 0 AS last2days, 0 AS last3days, cou
nt(*) AS last4days, 0 AS last5days FROM blogcomment WHERE commentdate > (sysdate
-4) GROUP BY blogname
  9          UNION ALL
10          SELECT blogname, 0 AS last1day, 0 AS last2days, 0 AS last3days, 0 A
S last4days, count(*) AS last5days FROM blogcomment WHERE commentdate > (sysdate
-5) GROUP BY blogname
11          )
12  GROUP BY blogname
13  ORDER BY blogname;

BLOGNAME
--------------------------------------------------------------------------------

  LAST1DAY  LAST2DAYS  LAST3DAYS  LAST4DAYS  LAST5DAYS
---------- ---------- ---------- ---------- ----------
Anwar Ibrahim
        0          0        28        125        212

Che Det
        0          0        23        227        227

I am a Malaysian
        0          0          1          1          1


BLOGNAME
--------------------------------------------------------------------------------

  LAST1DAY  LAST2DAYS  LAST3DAYS  LAST4DAYS  LAST5DAYS
---------- ---------- ---------- ---------- ----------
Kickdefella
        0          0          0          9        10

Lim Kit Siang
        0          0        115        375        610

Politics 101 Malaysia
        0          0        14        27        38


BLOGNAME
--------------------------------------------------------------------------------

  LAST1DAY  LAST2DAYS  LAST3DAYS  LAST4DAYS  LAST5DAYS
---------- ---------- ---------- ---------- ----------
Sassy MP
        0          0          1          4          9

Screenshots
        0          0          0          7        13

THE MALAYSIAN
        0          0          8        13        30


9 rows selected.

SQL> SELECT blogname,
  2        sum( CASE WHEN (sysdate-1) <= commentdate THEN 1 ELSE 0 END) last1day
,
  3        sum( CASE WHEN (sysdate-2) <= commentdate AND commentdate < (sysdate-
1) THEN 1 ELSE 0 END) last2days,
  4        sum( CASE WHEN (sysdate-3) <= commentdate AND commentdate < (sysdate-
2) THEN 1 ELSE 0 END) last3days,
  5        sum( CASE WHEN (sysdate-4) <= commentdate AND commentdate < (sysdate-
3) THEN 1 ELSE 0 END) last4days,
  6        sum( CASE WHEN (sysdate-5) <= commentdate AND commentdate < (sysdate-
4) THEN 1 ELSE 0 END) last5days
  7    FROM blogcomment
  8  GROUP BY blogname
  9  ORDER BY blogname;

BLOGNAME
--------------------------------------------------------------------------------

  LAST1DAY  LAST2DAYS  LAST3DAYS  LAST4DAYS  LAST5DAYS
---------- ---------- ---------- ---------- ----------
Anwar Ibrahim
        0          0        28        97        87

Che Det
        0          0        23        204          0

Demi Allah, Agama, Bangsa dan Negara
        0          0          0          0          0


BLOGNAME
--------------------------------------------------------------------------------

  LAST1DAY  LAST2DAYS  LAST3DAYS  LAST4DAYS  LAST5DAYS
---------- ---------- ---------- ---------- ----------
I am a Malaysian
        0          0          1          0          0

Kickdefella
        0          0          0          9          1

Lim Kit Siang
        0          0        115        260        235


BLOGNAME
--------------------------------------------------------------------------------

  LAST1DAY  LAST2DAYS  LAST3DAYS  LAST4DAYS  LAST5DAYS
---------- ---------- ---------- ---------- ----------
Politics 101 Malaysia
        0          0        14        13        11

Sassy MP
        0          0          1          3          5

Screenshots
        0          0          0          7          6


BLOGNAME
--------------------------------------------------------------------------------

  LAST1DAY  LAST2DAYS  LAST3DAYS  LAST4DAYS  LAST5DAYS
---------- ---------- ---------- ---------- ----------
THE MALAYSIAN
        0          0          8          5        17

Tian Chua
        0          0          0          0          0


11 rows selected.

Ja ok, jeg kan godt se at nummer 1 stadig laegger dem sammen og nummer to giver det korrekte... Sorry. Jeg var lige for hurtig der. Og det lader ogsaa til at nummer 1 ikke returnerer dem, som har 0 i alle kolonner...

Jeg bruger nummer to saa :)

Smider du et svar?

svar :-)

Du skal klikke paa den radio button, som siger 'Svar' :p

(rigtigt) svar :-)