Home > Linux > postgresql : SQL SELECT query

postgresql : SQL SELECT query

Examples:

SELECT con WHERE .. IN ( SELECT ) e SELECT con INNER JOIN

SELECT users_ltd.userid, users_ltd.name, users_ltd.password, users_ltd.email, 

FROM users_ltd
    INNER JOIN contacts
    ON users_ltd.email = contacts.email

WHERE users_ltd.email IN (
select  users_ltd.email
-- users_ltd.userid, contacts.itemid, contacts."IDUser", contacts.email
FROM users_ltd
  INNER JOIN contacts
  ON users_ltd.email = contacts.email
  WHERE internal IS NULL
  ORDER BY contacts.email )

SELECT con WHERE IN SELECT e secondo SELECT con INNER JOIN

SELECT email,internal FROM users_ltd 

WHERE email IN (
select  users_ltd.email
FROM users_ltd
  INNER JOIN contacts
  ON users_ltd.email = contacts.email
  WHERE internal IS NULL
  ORDER BY contacts.email )

SELECT con FROM su (SELECT con GROUPING ALIAS obbligatorio ) e con JOIN

SELECT DL.iduser, Dl.conta,C.firstname,C.lastname,C.name, C."Organization",C.email  
     FROM (SELECT  iduser, count (iduser) AS conta   FROM downloadlog  
           WHERE date > '01-01-2007'  
           GROUP BY iduser
           ORDER BY conta DESC ) AS DL
        JOIN contacts C
        ON C.itemid = DL.iduser

        WHERE conta > 5 ;

SELECT  usando alias per indicare le tabelle

SELECT C.email, U.email, C.countrycode, U."Country"
FROM contacts_c U
          JOIN contacts C
                   ON C.email = U.email </pre>

SELECT DISTINCT QUERY
dichiarazione precisa del tipo data
per poter operare correttamente confronti tra le date

CREATE OR REPLACE FUNCTION _getdownloadreport_timestamp(timestamp without time zone, timestamp without time zone, character varying)
  RETURNS SETOF downloadreports_user AS
$BODY

SELECT DISTINCT
       date_trunc('day', downloadlog.date),
       contacts.itemid,
       (contacts.lastname) AS name,
       contacts."Organization",
       contacts.email,

FROM downloadlog
  INNER JOIN contacts
    ON downloadlog.iduser = contacts.itemid
-- non ho sempre il campo country meglio fare una  LEFT JOIN
  LEFT JOIN  country
       ON UPPER(contacts.countrycode) = country.countrycode
--  INNER JOIN download
--    ON downloadlog.iddownload = download.itemid
      WHERE (downloadlog.date >= $1 AND downloadlog.date <= $2 )

ORDER BY name;

$BODY$
  LANGUAGE 'sql' VOLATILE SECURITY DEFINER
  COST 100
  ROWS 1000;

In postgresql IIF è sostituito da CASE .... WHEN .. THEN .. ELSE END
SELECT DISTINCT
       date_trunc('day', downloadlog.date),
       -- downloadlog.date,
       contacts.itemid,
         CASE contacts.lastname  IS Null WHEN TRUE THEN '' ELSE TRIM(contacts.lastname)  END ||
         CASE contacts.firstname IS Null WHEN TRUE THEN '' ELSE (' ' || TRIM(contacts.firstname)) END ||
         CASE contacts.name IS Null WHEN TRUE THEN '' ELSE TRIM(contacts.name) END AS veracity,
       contacts.email,
       contacts."Phone",

FROM downloadlog
  INNER JOIN contacts
    ON downloadlog.iduser = contacts.itemid
  LEFT JOIN  country
       ON UPPER(contacts.countrycode) = country.countrycode
    WHERE (downloadlog.date >= '1-1-2000'::Date AND downloadlog.date <= '1-1-2011'::date )
 ORDER BY name;

..

Risorse:

Postgresql 

Plpgsql 

Postgresql cancella record duplicati

Creare eliminare database e tabelle

Advertisements
Categorie:Linux Tag:, , ,
  1. Non c'è ancora nessun commento.
  1. No trackbacks yet.

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

%d blogger hanno fatto clic su Mi Piace per questo: